I've searched the board for solutions but I'm unable to find anything similar.
I'm trying to place about 5 pivot tables with varying rows of data below each other. I'm able to get it done with the first two, but the code is not working from the third on.
Sadly, I was asked to place them vertically across the sheet. Below is my piece of code. Sorry for the mess, but I'm kind of new to VBA. I'm using Excel 2007.
Sheets.ADD.Name = "Pivot"
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"CTB!" & Sheets("CTB").Range("A1").CurrentRegion.Address(ReferenceStyle:=xlR1C1), Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="Pivot!R1C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion14
Cells(1, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Collector")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Transaction #"), "Count of Transaction #", xlCount
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Invoice Amount"), "Sum of Invoice Amount", xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Total Balance"), "Sum of Total Balance", xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Days Late"), "Sum of Days Late", xlSum
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Days Late")
.Caption = "Average of Days Late"
.Function = xlAverage
End With
Dim pt As PivotTable
Set pt = Sheets("Pivot").PivotTables("PivotTable1")
Application.PivotTableSelection = True
pt.PivotSelect ("Sum of Total Balance"), xlDataOnly
Selection.Style = "Currency"
pt.PivotSelect ("Sum of Invoice Amount"), xlDataOnly
Selection.Style = "Currency"
pt.PivotSelect ("Average of Days Late"), xlDataOnly
Selection.NumberFormat = "0"
Dim LastRow As Long
LastRow = Sheets("Pivot").Cells(Sheets("Pivot").Rows.Count, "A").End(xlUp).Row
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"C121+!" & Sheets("C121+").Range("A1").CurrentRegion.Address(ReferenceStyle:=xlR1C1), Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:=Range("A" & LastRow + 2), TableName:="PivotTable2", DefaultVersion _
:=xlPivotTableVersion14
Dim pt2 As PivotTable
Set pt2 = Sheets("Pivot").PivotTables("PivotTable2")
pt2.TableRange1.Select
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Collector")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("Transaction #"), "Count of Transaction #", xlCount
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("Invoice Amount"), "Sum of Invoice Amount", xlSum
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("Total Balance"), "Sum of Total Balance", xlSum
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("Days Late"), "Sum of Days Late", xlSum
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Sum of Days Late")
.Caption = "Average of Days Late"
.Function = xlAverage
End With
Application.PivotTableSelection = True
pt2.PivotSelect ("Sum of Total Balance"), xlDataOnly
Selection.Style = "Currency"
pt2.PivotSelect ("Sum of Invoice Amount"), xlDataOnly
Selection.Style = "Currency"
pt2.PivotSelect ("Average of Days Late"), xlDataOnly
Selection.NumberFormat = "0"
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"11+!" & Sheets("11+").Range("A1").CurrentRegion.Address(ReferenceStyle:=xlR1C1), Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:=Range("A" & LastRow + 2), TableName:="PivotTable3", DefaultVersion _
:=xlPivotTableVersion14
Dim pt3 As PivotTable
Set pt3 = Sheets("Pivot").PivotTables("PivotTable3")
pt3.TableRange1.Select
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Collector")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
"PivotTable3").PivotFields("Transaction #"), "Count of Transaction #", xlCount
ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
"PivotTable3").PivotFields("Invoice Amount"), "Sum of Invoice Amount", xlSum
ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
"PivotTable3").PivotFields("Total Balance"), "Sum of Total Balance", xlSum
ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
"PivotTable3").PivotFields("Days Late"), "Sum of Days Late", xlSum
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Sum of Days Late")
.Caption = "Average of Days Late"
.Function = xlAverage
End With
Application.PivotTableSelection = True
pt3.PivotSelect ("Sum of Total Balance"), xlDataOnly
Selection.Style = "Currency"
pt3.PivotSelect ("Sum of Invoice Amount"), xlDataOnly
Selection.Style = "Currency"
pt3.PivotSelect ("Average of Days Late"), xlDataOnly
Selection.NumberFormat = "0"
Any advice will be greatly appreciated!
It looks like you have to update your LastRow variable each time you want to insert a new pivotTable. Then the next pivotTable will be inserted to a range beginning two lines under the last one...
You set the LastRow variable once before inserting the second pivotTable, but you have to reset it to read the current last row before you insert pivotTable3, etc, etc
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments