Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Aug 2013
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel: Pivot Table with Multiple Consolidation Ranges

    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 <filepath/name>. 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.

  2. #2
    New Lounger
    Join Date
    Aug 2013
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts
    SOLVED.

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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •