VBA 2nd Pivot table creation error

[jamescooper]

Hello,

I have the following code that works to create the first pivot table but fails on the second pivot table at the line:

'Create Pivot table from Pivot Cache

  Set pvt = pvtCache.CreatePivotTable( _
    TableDestination:=StartPvt, _
    TableName:="PivotTable2")

What do I need to change to resolve please?

Sub Pivot_Table()


Dim sht As Worksheet
Dim pvtCache As PivotCache
Dim pvt As PivotTable
Dim StartPvt As String
Dim SrcData As String
Dim PF As PivotField
Dim PI As PivotItem


Application.Calculation = xlManual


Last_Row = Sheets("Calculations").Range("A" & Rows.Count).End(xlUp).Row


'Determine the data range you want to pivot
  SrcData = ActiveSheet.Name & "!" & Range("A2:AE" & Last_Row).Address(ReferenceStyle:=xlR1C1)


'Create a new worksheet
  Set sht = Sheets.Add


'Where do you want Pivot Table to start?
  StartPvt = sht.Name & "!" & sht.Range("A3").Address(ReferenceStyle:=xlR1C1)


'Create Pivot Cache from Source Data
  Set pvtCache = ActiveWorkbook.PivotCaches.create( _
    SourceType:=xlDatabase, _
    SourceData:=SrcData)


'Create Pivot table from Pivot Cache
  Set pvt = pvtCache.CreatePivotTable( _
    TableDestination:=StartPvt, _
    TableName:="PivotTable1")
        
pvt.ManualUpdate = False
        
Set pvt = ActiveSheet.PivotTables("PivotTable1")
    
  'Add item to the Report Filter
  
    pvt.PivotFields("Origin").Orientation = xlRowField
    pvt.PivotFields("Destination").Orientation = xlRowField
    pvt.PivotFields("Profit Centre").Orientation = xlRowField
    pvt.PivotFields("Direction").Orientation = xlRowField
    pvt.RowAxisLayout xlTabularRow
    pvt.ColumnGrand = False
    pvt.RowGrand = False
    pvt.RepeatAllLabels xlRepeatLabels
    
    pvt.PivotFields("Origin").Subtotals(1) = False
    pvt.PivotFields("Destination").Subtotals(1) = False
    pvt.PivotFields("Profit Centre").Subtotals(1) = False
    pvt.PivotFields("Direction").Subtotals(1) = False
      
On Error GoTo Err1
    
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Direction")
        .PivotItems("#N/A").Visible = True
  
        For Each PI In .PivotItems
            If PI.Name <> "#N/A" Then
                PI.Visible = False
            End If
        Next
  
    End With


Err1:


ActiveSheet.Name = "Errors"


With Worksheets("Errors").Range("A1:D100000")
 .Font.Size = 8
End With


Worksheets("Errors").Range("A1:D100000").Columns.AutoFit


Sheets("Errors").Tab.ColorIndex = 3


Call Pivot_Table_2


End Sub


Sub Pivot_Table_2()


Dim sht As Worksheet
Dim pvtCache As PivotCache
Dim pvt As PivotTable
Dim StartPvt As String
Dim SrcData As String
Dim PF As PivotField
Dim PI As PivotItem


Application.Calculation = xlManual


Last_Row = Sheets("Calculations").Range("A" & Rows.Count).End(xlUp).Row


'Determine the data range you want to pivot
  SrcData = ActiveSheet.Name & "!" & Range("A2:AE" & Last_Row).Address(ReferenceStyle:=xlR1C1)


'Create a new worksheet
  Set sht = Sheets.Add


'Where do you want Pivot Table to start?
  StartPvt = sht.Name & "!" & sht.Range("A3").Address(ReferenceStyle:=xlR1C1)


'Create Pivot Cache from Source Data
  Set pvtCache = ActiveWorkbook.PivotCaches.create( _
    SourceType:=xlDatabase, _
    SourceData:=SrcData)


'Create Pivot table from Pivot Cache
  Set pvt = pvtCache.CreatePivotTable( _
    TableDestination:=StartPvt, _
    TableName:="PivotTable2")
        
pvt.ManualUpdate = False
        
Set pvt = ActiveSheet.PivotTables("PivotTable2")
    
  'Add item to the Report Filter
  
    pvt.PivotFields("Point 1").Orientation = xlRowField
    pvt.PivotFields("Point 1 Stanox Code").Orientation = xlRowField
    pvt.PivotFields("Point 2").Orientation = xlRowField
    pvt.PivotFields("Point 2 Stanox Code").Orientation = xlRowField
    pvt.PivotFields("Mileage").Orientation = xlRowField
    pvt.RowAxisLayout xlTabularRow
    pvt.ColumnGrand = False
    pvt.RowGrand = False
    pvt.RepeatAllLabels xlRepeatLabels
    
    pvt.PivotFields("Point 1").Subtotals(1) = False
    pvt.PivotFields("Point 1 Stanox Code").Subtotals(1) = False
    pvt.PivotFields("Point 2").Subtotals(1) = False
    pvt.PivotFields("Point 2 Stanox Code").Subtotals(1) = False
      
On Error GoTo Err1
    
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Mileage")
        .PivotItems("#N/A").Visible = True
  
        For Each PI In .PivotItems
            If PI.Name <> "#N/A" Then
                PI.Visible = False
            End If
        Next
  
    End With


Err1:


ActiveSheet.Name = "Mileage Errors"


With Worksheets("Mileage Errors").Range("A1:D100000")
 .Font.Size = 8
End With


Worksheets("Mileage Errors").Range("A1:D100000").Columns.AutoFit


Sheets("Mileage Errors").Tab.ColorIndex = 3


End Sub

 

[jamescooper]

Any ideas here, please?