I'm trying to use a Macro to create two PivotTables on a new sheet
I get a break here
I'm guessing because it's pointing to Sheet1 and the first PivotTable wasn't created on Sheet1, so I need to make them both point to the same new sheet...
I just want the second PivotTable to appear 2 lines down from the last row of the first PivotTable, but starting in column 2 instead of 1
I got what code I have from recording a Macro
I'm new to Excel Programming and thought I could record the Macro and then tweak the generated code
Thanks in advance for any helpful feedback!
Code:
Sub allBySLS()
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
ActiveSheet.Cells(1).CurrentRegion.Address).CreatePivotTable TableDestination:="", _
TableName:="Invoices-2008", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTables("Invoices-2008").PivotFields("MONTH")
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("Invoices-2008").PivotFields("SAL TER")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("Invoices-2008").AddDataField ActiveSheet.PivotTables( _
"Invoices-2008").PivotFields("INVOICE AMOUNT"), "Sum of INVOICE AMOUNT", xlSum
ActiveSheet.PivotTables("Invoices-2008").AddDataField ActiveSheet.PivotTables( _
"Invoices-2008").PivotFields("COMM AMOUNT"), "Sum of COMM AMOUNT", xlSum
ActiveSheet.PivotTables("Invoices-2008").AddDataField ActiveSheet.PivotTables( _
"Invoices-2008").PivotFields("COMM %"), "Sum of COMM %", xlSum
Range("B5").Select
With ActiveSheet.PivotTables("Invoices-2008").PivotFields("Sum of INVOICE AMOUNT" _
)
.NumberFormat = "$#,##0.00"
End With
Range("B6").Select
With ActiveSheet.PivotTables("Invoices-2008").PivotFields("Sum of COMM AMOUNT")
.NumberFormat = "$#,##0.00"
End With
Range("B7").Select
With ActiveSheet.PivotTables("Invoices-2008").PivotFields("Sum of COMM %")
.Function = xlAverage
.NumberFormat = "0.00"
End With
Range("C3").Select
ActiveSheet.PivotTables("Invoices-2008").PivotFields("MONTH").Caption = "2008"
Sheets("invoices-2007").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'invoices-2007'!R1C1:R40945C14").CreatePivotTable TableDestination:= _
"'[invoices-2008.xls]Sheet1'!R81C2", TableName:="Invoices-2007", _
DefaultVersion:=xlPivotTableVersion10
With ActiveSheet.PivotTables("Invoices-2007").PivotFields("MONTH")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("Invoices-2007").AddDataField ActiveSheet.PivotTables( _
"Invoices-2007").PivotFields("INVOICE AMOUNT"), "Sum of INVOICE AMOUNT", xlSum
ActiveSheet.PivotTables("Invoices-2007").AddDataField ActiveSheet.PivotTables( _
"Invoices-2007").PivotFields("COMM AMOUNT"), "Sum of COMM AMOUNT", xlSum
ActiveSheet.PivotTables("Invoices-2007").AddDataField ActiveSheet.PivotTables( _
"Invoices-2007").PivotFields("COMM %"), "Sum of COMM %", xlSum
Range(LastRow + 1, 3).Select
ActiveSheet.PivotTables("Invoices-2007").PivotFields("MONTH").Caption = "2007"
Range(LastRow + 1, 2).Select
With ActiveSheet.PivotTables("Invoices-2007").PivotFields("Sum of INVOICE AMOUNT" _
)
.NumberFormat = "$#,##0.00"
End With
Range(LastRow + 1, 2).Select
With ActiveSheet.PivotTables("Invoices-2007").PivotFields("Sum of COMM AMOUNT")
.NumberFormat = "$#,##0.00"
End With
Range(LastRow + 1, 2).Select
With ActiveSheet.PivotTables("Invoices-2007").PivotFields("Sum of COMM %")
.Function = xlAverage
.NumberFormat = "0.00"
End With
End Sub
I get a break here
Code:
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'invoices-2007'!R1C1:R40945C14").CreatePivotTable TableDestination:= _
"'[invoices-2008.xls]Sheet1'!R81C2", TableName:="Invoices-2007", _
DefaultVersion:=xlPivotTableVersion10
I'm guessing because it's pointing to Sheet1 and the first PivotTable wasn't created on Sheet1, so I need to make them both point to the same new sheet...
I just want the second PivotTable to appear 2 lines down from the last row of the first PivotTable, but starting in column 2 instead of 1
I got what code I have from recording a Macro
I'm new to Excel Programming and thought I could record the Macro and then tweak the generated code
Thanks in advance for any helpful feedback!