Creating consolidation Pivot Table using VBS

Question

I hope some one can help me. I have a workbook that I am copying several worksheets into using VBA and am wanting to update a consolidation Pivot Table from data on each sheet. I have some code I pieced together to create the array that would go after SourceData:= in the following piece of code

Sheets("Pivot Table").Select

ActiveSheet.PivotTableWizard SourceType:=xlConsolidation, SourceData:=Array(Array("'0342'!R1C4:R65C75", "0342"), Array("'0370'!R1C4:R65C75", "0370"),Array("'0411'!R1C4:R65C75", "0411"))

If I leave the array hard coded like above the code will run but if i create a varaiable and assign SheetArray to equal the string that gets created from other code and change SourceData:=SheetArray i get an error. The string that gets created is the same Array as above. The error is Run-Time Error '1004': Cannot open PivotTable Source file 'Array(Array(".xls'0342". I am a total loss what I need to do to correct this.

Answer 1

Hi,

To address your concern about creating consolidation pivot table using VBS, we suggest that you post your query on Technet Community Forum.

Regards.

Answer 2

Is there a specific format for the SourceData on a consolidation Pivot Table.  I have recorded a macro using the wizard and have seen two different formats that gets applied to SoureData

1: Array(Array("D0342!R1C4:R51C52", "D0342"), Array("D0370!R1C4:R56C52", "D0370"), Array("D0411!R1C4:R42C52", "D0411"))

2: Array(Array("'D0342'!R1C4:R51C52", "D0342"), Array("'D0370'!R1C4:R56C52", "D0370"), Array("'D0411'!R1C4:R42C52", "D0411"))