Anyone know how to set the source of one pivottable to that of another using VBA?
Printable View
Anyone know how to set the source of one pivottable to that of another using VBA?
Bush, you can record a macro to get what you want.
If you are setting up the 1st pivot using vba then store the source in a variable and use that variable to set the source of the 2nd pivot...
For example
Code:'~~> Any cell in the 2nd Pivot. I have taken H10 as example
Range("H10").Select
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:=MyVariable
Yeah, I've had a crack with that before - the code it gives you when you record the macro can't be applied it just errors for me.
The code it gives me:So I amend it to the below and try it on a different sheet:Code:Range("E8").Select
ActiveSheet.PivotTableWizard SourceType:=xlPivotTable, SourceData:= _
"PivotTable1"
but that gives error PivotTableWizard method of Worksheet class failedCode:Worksheets("Sheet1").Activate
ActiveSheet.Range("E8").Select
ActiveSheet.PivotTableWizard SourceType:=xlPivotTable, SourceData:="PivotTable1"
alternatively I've tried:which gives and Application-defined or object-defined errorCode:Range("E8").Select
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
Workseets("Sheet1").PivotTables("PivotTable1").SourceData
No Bush... I didn't mean that...
How are you creating the 1st pivot?
This would give you an error as it requires a range or a variable which has range address stored in it...
Quote:
SourceData:="PivotTable1"
both pivots already exist in the spreadsheet
This is to get the names of the pivot tables in your excel sheet
Once you have the names simply replace it in the code below...Code:Sub GetPivotNames()
Dim pvt As PivotTable
'~~> Getting the names of your Pivot
For Each pvt In ActiveSheet.PivotTables
MsgBox pvt.Name
Next
End Sub
Code:Sub GetSetPivotSourceData()
Dim pvt As PivotTable, rng1 As String
'~~> Getting the sourcedata from the first Pivot
For Each pvt In ActiveSheet.PivotTables
If Trim(pvt.Name) = "PivotTable1" Then
rng1 = pvt.SourceData
End If
Next
'~~> Setting the sourcedata for the 2nd Pivot
For Each pvt In ActiveSheet.PivotTables
If Trim(pvt.Name) = "PivotTable2" Then
pvt.SourceData = rng1
End If
Next
End Sub
That doesn't seem to work. I get an "Application-defined or object defined error" trying to access the Source Data directly e.g.:However, I think I've solved my problem another way. I was trying to change the access database source of one pivottable (by changing the connection string) and refresh all the other pivots at the same time (by maintaining the link between the pivots).Code:Dim S As String
S = Worksheets("Sheet1").PivotTables("PivotTable1").SourceData
If you do this manually through the front end then it dissociates the pivot you've just refreshed from the others, so I was trying to repoint all the other pivots back to the refreshed one.
But by making the change directly on the PivotCache connection string, they all seem to have updated:Code:Worksheets("Sheet1").PivotTables("PivotTable1").PivotCache.Connection = 'New Connection
Worksheets("Sheet1").PivotTables("PivotTable1").RefreshTable
Another way to do it...
vb Code:
Sub Sample() Dim pvt As PivotTable, pvt1 As PivotTable Set pvt = Worksheets("Sheet1").PivotTables("PivotTable1") Set pvt1 = Worksheets("Sheet1").PivotTables("PivotTable2") pvt1.SourceData = pvt.SourceData End Sub