Programmatically create multiple PivotTables on New Sheet

[drocco]

I'm trying to use a Macro to create two PivotTables on a new sheet

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

    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!

 

[drocco]

Anybody?

 

[drocco]

Still looking to figure this one out