Creating two pivot tables on same sheet - Optimize recorded macro code

Hi all,
I am trying to record a macro to create two pivot tables on the same sheet.
I need your suggestions to optimize the code.
Also, there are two issues that I'll like to get sorted out
1. The macro always throws an error at the point where the pivot table is being inserted.
2. The number of rows vary every month and I don't know how to optimize the code to accordingly.

Please find attached the raw data and code for your reference.

Sub Macro3()
    'Inserting First Pivot Table
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Raw Data!R1C1:R99C4", Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:="Raw Data!R1C6", TableName:="PivotTable8", _
        DefaultVersion:=xlPivotTableVersion14
    Sheets("Raw Data").Select
    Cells(1, 6).Select
    With ActiveSheet.PivotTables("PivotTable8").PivotFields("Email ID")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable8").AddDataField ActiveSheet.PivotTables( _
        "PivotTable8").PivotFields("Downloads"), "Sum of Downloads", xlSum
    ActiveSheet.PivotTables("PivotTable8").AddDataField ActiveSheet.PivotTables( _
        "PivotTable8").PivotFields("Views"), "Sum of Views", xlSum
    
    'Copy paste special of pivot table
    Columns("F:H").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
    'Performing vlookup to obtain respective codes of Email IDs
    Range("I1").Select
    ActiveCell.FormulaR1C1 = "Code"
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],R2C1:R99C2,2,0)"
    Range("I2").Select
    Selection.AutoFill Destination:=Range("I2:I45")
    Range("I2:I45").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    'Making Headings and Grand Totals in Bold
    Range("F1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Font.Bold = True
    Selection.End(xlDown).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range("F45:H45").Select
    Selection.Font.Bold = True
    
    'Deleting #N/A under code, against Grand Total
    Range("I45").Select
    Selection.ClearContents

    'Inserting Second Pivot Table
    Range("F1:I44").Select
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Raw Data!R1C6:R44C9", Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:="Raw Data!R1C11", TableName:="PivotTable9", _
        DefaultVersion:=xlPivotTableVersion14
    Sheets("Raw Data").Select
    Cells(1, 11).Select
    With ActiveSheet.PivotTables("PivotTable9").PivotFields("Code")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable9").AddDataField ActiveSheet.PivotTables( _
        "PivotTable9").PivotFields("Row Labels"), "Count of Row Labels", xlCount
    ActiveSheet.PivotTables("PivotTable9").AddDataField ActiveSheet.PivotTables( _
        "PivotTable9").PivotFields("Sum of Downloads"), "Sum of Sum of Downloads", _
        xlSum
    ActiveSheet.PivotTables("PivotTable9").AddDataField ActiveSheet.PivotTables( _
        "PivotTable9").PivotFields("Sum of Views"), "Sum of Sum of Views", xlSum
    
    'Copy paste special of second Pivot Tables
    Columns("K:N").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("K1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Font.Bold = True
    Selection.End(xlDown).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Font.Bold = True
    Range("O1").Select
End Sub

Thanks and regards,
johnny_tc

Hi, johnny_tc,

you use the constants PivotTable8 and PivotTable9 for thenames of the pivot tables. As long as these pivot tables still are in the workbook an exception should be trhown up.

Alternative: use constants for the names of the pivottables and alter the code where the old constants are used.

Regarding the varying ranges of data:

Const cstrPT_One As String = "New PT 1"
    
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        Sheets("Raw Data").Range("A1").CurrentRegion.Address(ReferenceStyle:=xlR1C1), Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:="Raw Data!R1C6", _
        TableName:=cstrPT_One, _
        DefaultVersion:=xlPivotTableVersion14
    Sheets("Raw Data").Select
    Cells(1, 6).Select
    With ActiveSheet.PivotTables(cstrPT_One).PivotFields("Email ID")
        .Orientation = xlRowField
        .Position = 1
    End With
'more code

Ciao,
Holger

Thanks a lot Holger. I will have a look at this code and will let you know of the progress.

Meanwhile there is one more issue that I'm facing.
While trying to insert the second pivot table, it is automatically taking the Grand Totals at the bottom.
Is there any way to avoid this while inserting the second pivot???

Hi, johnny_tc,

first of all the sheetname should be altered to "'Raw Data'" instead of "Raw Data".

Second: if you know the name of the PT as you have passed it in (like in the example):

    With ActiveSheet.PivotTables(cstrPT_One)
        .ColumnGrand = False
        .RowGrand = False
    End With

Ciao,
Holger