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
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