VBA change Pivot Table Source Sheet

Question

I've created a program that copies over a pivot table from another workbook, but I need it to refer to the worksheet in the current file.

So for example, in the original file it refers to "QueryResults" as the source, and I need it to refer to the "QueryResults" in the new file after it is transferred over. Does anyone have any ideas on how to do this?

I've tried unsuccessfully to recreate the cache, but get an error when running it in my code.

        ActiveWorkbook.PivotCaches.Create _
        (SourceType:=xlDatabase, SourceData:=Worksheets("QueryResults").Range("A1:AY" & _
        Worksheets("QueryResults").Cells(Rows.Count, 1).End(xlUp).Row).Address(External:=True), _
        Version:=xlPivotTableVersion14)

Dy.Lee Dy.Lee · Accepted Answer · 2018-12-18T01:14:43

Try

Sub setPivot()
    Dim pv As PivotTable
    Dim Ws As Worksheet
    Dim wsData As Worksheet
    Dim rngDB As Range, strRng As String
    Dim r As Long

    Set Ws = ActiveSheet
    Set pv = Ws.PivotTables(1)

    Set wsData = Sheets("QueryResults")

    With wsData
        r = .Range("a" & Rows.Count).End(xlUp).Row
        Set rngDB = .Range("A1:AY" & r)
    End with
    strRng = rngDB.Address(, , xlR1C1, 1) 'not xlA1
    With pv
        .SourceData = strRng
        .RefreshTable
    End With
End Sub

VBA change Pivot Table Source Sheet

1 Answers