PDA

View Full Version : Help Creating Pivot Table w/ Specific Consolidation Ranges



Bobbymao
01-04-2007, 09:09 PM
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....:mkay

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("Sales" _
).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

XLGibbs
01-05-2007, 06:17 PM
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?

Bobbymao
01-06-2007, 11:32 PM
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!!:*)

XLGibbs
01-07-2007, 12:12 AM
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..

Bobbymao
01-15-2007, 07:12 PM
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.