CODEWITHSUNDEEP
excelvba
whytheq
whytheq

Reputation: 35557

Pivot Table still refers to previous workbook named range

This is a scenario I've encountered pretty regularly - and worked around it rather than try to solve it.

I have a control workbook with the following worksheets

enter image description here

In the workbook is a named range "myData" which is the data in sheet "Data".

The VBA gets data and pastes it in "Data" then renames the range then refreshes the pivot table which uses the named range as its source. Then the routine copies the sheets Array("Pivot", "Data", "notes") into a fresh workbook which gets saved in a different location as "NewWorkbook.xlsx"

Now if I open the NewWorkbook and look at the data source for the pivot it is referring to the original control workbook.

Is there a simple way of making sure the pivot refers to the data in the new workbook? Should I not use a named range?

Upvotes: 0

Views: 2078

Answers (2)

ckm1989
ckm1989

Reputation: 37

You can also click on the Pivot table tab and redirect it's source from there using the "change source" button and then refreshing as well.enter image description here

Hope this helps.

Upvotes: 1

JosieP
JosieP

Reputation: 3410

You need to update the sourcedata of the pivotcache

Sheets(Array("Data", "Pivot", "notes")).Copy
With ActiveWorkbook.Sheets("Pivot").PivotTables(1)
    .PivotCache.SourceData = "myData"
    .RefreshTable
End With

for instance

Upvotes: 1

Related Questions