Excel: Pivot Table with Multiple Consolidation Ranges

Topic

New Reply

I want to build a pivot table based on multiple consolidation ranges. Specifically, the same cells across several worksheets. The names of the worksheets are stored in a table called loSheetNames, so I need a way in the code to add them to the pivot table.

When I record the steps, the statement that actually makes the table is this:

Code:

 ActiveWorkbook.PivotCaches.Create(SourceType:=xlConsolidation, SourceData:= _
        Array(“‘UIC 1’!R1C5:R102C10”, “‘UIC 2’!R1C5:R102C10”, “‘UIC 3’!R1C5:R102C10”), Version:= _
        xlPivotTableVersion15).CreatePivotTable TableDestination:= _
        “‘[Checklists.xlsm]Units’!R1C13”, TableName:= _
        “PivotTable7”, DefaultVersion:=xlPivotTableVersion15

Now, this also works:

Code:

   aray = Array(“‘UIC 1’!R1C5:R102C10”, “‘UIC 2’!R1C5:R102C10”, “‘UIC 3’!R1C5:R102C10”)

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlConsolidation, SourceData:= aray, _
         Version:=xlPivotTableVersion15). _
        CreatePivotTable TableDestination:= _
        “‘[ChecklistsV15.xlsm]Sheet1’!R3C1″, TableName:=”PivotTable7”, _
        DefaultVersion:=xlPivotTableVersion15

So I needed a way to assemble the ‘aray’ statement. I tried this:

Code:

For Each rngCurrentSheetName In loSheetNames.DataBodyRange
    lngCurrentIndex = rngCurrentSheetName.Row – TopRowofLO ‘+ 1
    aray(lngCurrentIndex) = rngCurrentSheetName & “‘!R1C6:R102C10”
Next rngCurrentSheetName

What’s surprising is this code DOES make the pivot table correctly. However, if I try to save the workbook, I get this error:

“Errors were detected while saveing . Microsoft Excel may be able to save teh file by removing or repairing some features…”

The repair never works; I can only save the file if I delete the pivot table.

What’s wrong with the array statement? Is there another way to add the worksheets to the pivot table?

Thanks.

Reply | Quote

Replies

SOLVED.

The first item in the array was a Null. I didn’t think to add Option Base 1 to the module!

Reply | Quote