Monday, July 27, 2009

How to change the Source of Pivot Table using VBA

How to configure Pivot Table source data externally through VBA

A Pivot Table is linked to a particular source data. If for some reasons, you need that to be configured by users the following code will give some hint:

Sub Change_Pivot_TableDataSource()

Dim oPT As PivotTable

Dim oPC As PivotCache

Dim ORange As Range

Set oPT = ActiveSheet.PivotTables(1)

Set oPC = oPT.PivotCache

Set ORange = Application.InputBox(Prompt:="Select the New DataRange", Type:=8)

oPC.SourceData = "Sheet1!" & Application.ConvertFormula(ORange.Address, xlA1, xlR1C1)

oPT.RefreshTable

If Not oPT Is Nothing Then Set oPT = Nothing

If Not oPC Is Nothing Then Set oPC = Nothing

End Sub

The code gets the new data range through Input Box and modifies the SourceData of the Pivot Table. Change the Sheet name accordingly before you use the code.

4 comments:

  1. Anonymous7:25 AM

    oPC.SourceData = "Sheet1!" & Application.ConvertFormula(ORange.Address, xlA1, xlR1C1)


    It is not working

    ReplyDelete
  2. Not Working... The line mentioned in the comment before mine is wrong

    ReplyDelete
  3. Can you post the code and the error you get while you run . It hsould be helpful

    ReplyDelete
  4. Anonymous7:37 AM

    I am also getting an error msg on the same line:
    oPC.SourceData = "Category_Variance" & Application.ConvertFormula(ORange.Address, xlA1, xlR1C1)

    Runtime error '1004'
    Reference is not valid

    ReplyDelete

StumbleUpon
Share on Facebook
Related Posts Plugin for WordPress, Blogger...
Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.