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 · Accepted Answer · 2018-12-18 02:56:49Z

0

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

edited Dec 18, 2018 at 2:56

answered Dec 18, 2018 at 1:14

Dy.Lee

7,5471 gold badge14 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA change Pivot Table Source Sheet

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related