Consulting

Results 1 to 5 of 5

Thread: Help Creating Pivot Table w/ Specific Consolidation Ranges

  1. #1
    VBAX Newbie
    Joined
    Jan 2007
    Posts
    3
    Location

    Red face Help Creating Pivot Table w/ Specific Consolidation Ranges

    I am trying to write code that will create a multiple consolidation range pivot table only if certain ranges exist. I tried writing one that would include all 10 possible ranges on 10 sheets, but if one sheet/range is missing the code will fail. The following code will create the pivot table the way I want it, but I need to add something that will take into account some of the sheets may be missing. Perhaps some sort of nested If...then statement? Please help....

    Running Excel 2003

    Present Code:

    Sub PivotRange()
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlConsolidation, SourceData:= _
    Array(Array("'1'!R9C1:R33C11", "Item1"), Array("'10'!R9C1:R33C11", "Item2"), Array( _
    "'2'!R9C1:R33C11", "Item3"), Array("'3'!R9C1:R33C11", "Item4"), Array( _
    "'4'!R9C1:R33C11", "Item5"), Array("'5'!R9C1:R33C11", "Item6"), Array( _
    "'6'!R9C1:R33C11", "Item7"), Array("'7'!R9C1:R33C11", "Item8"), Array( _
    "'8'!R9C1:R33C11", "Item9"), Array("'9'!R9C1:R33C11", "Item10"))). _
    CreatePivotTable TableDestination:="", TableName:="PivotTable4", _
    DefaultVersion:=xlPivotTableVersion10
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable4")
    .DisplayErrorString = True
    .RowGrand = False
    End With
    ActiveSheet.PivotTables("PivotTable4").DataPivotField.PivotItems("Sum of Value" _
    ).Position = 1
    Range("I4").Select
    ActiveSheet.PivotTables("PivotTable4").PivotFields("Column").PivotItems("Sa les" _
    ).Position = 2
    Range("F4").Select
    ActiveSheet.PivotTables("PivotTable4").PivotFields("Column").PivotItems( _
    "Debits").Position = 4
    ActiveSheet.PivotTables("PivotTable4").PivotFields("Column").PivotItems( _
    "Credits").Position = 8
    Range("G4").Select
    ActiveSheet.PivotTables("PivotTable4").PivotFields("Column").PivotItems( _
    "Ending Balance").Position = 9

    End Sub
    Last edited by Bobbymao; 01-04-2007 at 09:42 PM.

  2. #2
    VBAX Master XLGibbs's Avatar
    Joined
    Jan 2006
    Location
    state of confusion, but vacation in denial
    Posts
    1,315
    Location
    So you want it to populate the array only if the ranges exist?

    Can the ranges be named ranges? or will the sheets potentially not exist either?
    If you have posted the same question at multiple forums, please read this IMPORTANT INFO.

    Please use the thread tools to mark your thread Solved


    Please review the Knowledge Base
    for samples and solutions , or to submit your own!




  3. #3
    VBAX Newbie
    Joined
    Jan 2007
    Posts
    3
    Location

    Response to question

    The ranges can be named ranges; however, the sheets themselves may not exist. To be more specific, I am trying to create a userform that gives the user the option of adding additional worksheets for each Company being examined, whether there is 1 or 10 Companies. I want the data to be consolidated based on the number of specific ranges on specific sheets present in the workbook. Initially, before I knew anything about VBA, I had 10 command buttons with 10 macros and based on the button presssed (1 - 10), a specific number of sheets would be added along with an additional sheet that consolidated the data. Instead of doing this, i was hoping to just have the userform that I created add the number of sheets and then consolidate the data on another sheet.

    Thanks for responding!!

  4. #4
    VBAX Master XLGibbs's Avatar
    Joined
    Jan 2006
    Location
    state of confusion, but vacation in denial
    Posts
    1,315
    Location
    OKay, I think I can manage this, but are the sheets 1 to 10 added incrementally? meaning, are they added in numerical order?

    It will be a little trickier if there could be sheets "2" and sheets "10" but not sheets "1"...but can still be done..I just need to know..
    If you have posted the same question at multiple forums, please read this IMPORTANT INFO.

    Please use the thread tools to mark your thread Solved


    Please review the Knowledge Base
    for samples and solutions , or to submit your own!




  5. #5
    VBAX Newbie
    Joined
    Jan 2007
    Posts
    3
    Location
    The sheets 1 to 10 do not have to be added incrementally. There could be sheets 1 to 10 or 1 to 9 or 1 to 4, but they can be added in any order.

Posting Permissions

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