Pivot Table Source will not change Workbook in VBA?

0
votes

Okay, this has been bugging me for a couple of days now. Hopefully someone can spot a silly mistake somewhere?

The Premise:

I have a workbook, with data in. One of the tabs has a list of data in Columns A-D, and a Pivot Table based on that anchored in cell F3. One of the Macros in the workbook will Copy that worksheet (and several others, irrelevant to the question) to a new Workbook with ThisWorkbook.Worksheets(aTMP).Copy After:=wkb.Worksheets(wkb.Worksheets.Count), where aTMP is an Array that contains the Worksheet names.

Unfortunately, this leaves the PivotTable in the new Workbook pointed at the Table in the original Worksheet/Workbook - not the new one. "No problem!", I thought. "I'll just change the .PivotCache.SourceData to point to the correct file - it'll be easy". Boy was I wrong...

The Code:

(Including Debug.Print statements I added while trying to solve this myself) 

Debug.Print wkb.Worksheets(wsTableAndPivot.Name).PivotTables(1).PivotCache.SourceData
    'This is "'Z:\FilePath\[WorkbookName]Calculate_PivotTable'!C1:C4"
Debug.Print wkb.Worksheets(wsTableAndPivot.Name).Columns("A:D").Address(True, True, xlR1C1, True)
    'This is "[Book1]Calculate_PivotTable!C1:C4"
Debug.Print wkb.Worksheets(wsTableAndPivot.Name).PivotTables(1).PivotCache.SourceType
    ' This is 1
''''
wkb.Worksheets(wsTableAndPivot.Name).PivotTables(1).PivotCache.SourceData = _
    wkb.Worksheets(wsTableAndPivot.Name).Columns("A:D").Address(True, True, xlR1C1, True)
    'This is the line that throws an error
''''
Debug.Print "-----"
    'The following values are after I MANUALLY intervene, as detailed in "The Error"
Debug.Print wkb.Worksheets(wsTableAndPivot.Name).PivotTables(1).PivotCache.SourceData
    'This is "Calculate_PivotTable'!C1:C4"
Debug.Print wkb.Worksheets(wsTableAndPivot.Name).PivotTables(1).PivotCache.SourceType
    ' This is 1

The Error:

If I just run the code, I get this rather unhelpful error:

Error Number -1004
Application-defined or object-defined error

However, if I put a Break Point on the line that throws the error, manually change the Source Data to point to the same Workbook (e.g. Range A1:D2), and then run the line, it works properly and sets the Source Data to the entire columns.

(N.B. As I know that Protected Sheets can cause issues with PivotTables I have already checked that there aren't any)

1
excelvbapivot-table
@FreeMan Creating a new PivotCache seems to have done the trick - I guess there was sort of reference or lock based on the original workbook. Many thanks! – Chronocidal

1 Answers

1
votes

I ran into a similar problem and posted about it here at SO.

You can see if the solution I came up with will work for you.

Essentially, I created a new Pivot Cache for each Pivot Table from the data on the worksheet. Probably not optimum, but it was the only thing I found that worked.