User login

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()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

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.

Code/Templates/Informaiton provided on this site are without any liabilities. Owner of this website in no event will be held reponsible to damages resulting by using the code/templates/applications/
Copyright © 2003 xlMacros.com - Learn Excel VBA
Microsoft Excel is a U.S. registered trademark of Microsoft Corporation

Excel for Dummies
Spreadsheets for Dummies