Pivot Table Source

[bushmobile]

Anyone know how to set the source of one pivottable to that of another using VBA?

[Siddharth Rout]

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

'~~> Any cell in the 2nd Pivot. I have taken H10 as example
Range("H10").Select
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:=MyVariable

[bushmobile]

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:

    Range("E8").Select
    ActiveSheet.PivotTableWizard SourceType:=xlPivotTable, SourceData:= _
        "PivotTable1"

So I amend it to the below and try it on a different sheet:

    Worksheets("Sheet1").Activate
    ActiveSheet.Range("E8").Select
    ActiveSheet.PivotTableWizard SourceType:=xlPivotTable, SourceData:="PivotTable1"

but that gives error PivotTableWizard method of Worksheet class failed

alternatively I've tried:

    Range("E8").Select
    ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
        Workseets("Sheet1").PivotTables("PivotTable1").SourceData

which gives and Application-defined or object-defined error

[Siddharth Rout]

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...

SourceData:="PivotTable1"

[bushmobile]

both pivots already exist in the spreadsheet

[Siddharth Rout]

This is to get the names of the pivot tables in your excel sheet

Sub GetPivotNames()
    Dim pvt As PivotTable
    
    '~~> Getting the names of your Pivot
    For Each pvt In ActiveSheet.PivotTables
        MsgBox pvt.Name
    Next
End Sub

Once you have the names simply replace it in the code below...

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

[bushmobile]

That doesn't seem to work. I get an "Application-defined or object defined error" trying to access the Source Data directly e.g.:

Dim S As String
S = Worksheets("Sheet1").PivotTables("PivotTable1").SourceData

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).

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:

    Worksheets("Sheet1").PivotTables("PivotTable1").PivotCache.Connection = 'New Connection
    Worksheets("Sheet1").PivotTables("PivotTable1").RefreshTable

[Siddharth Rout]

Another way to do it...

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