Run time 1004 error

[zcahlmc]

I am getting a 1004 error on the below code.

I'm trying to create a pivot table based on data in the tab BRR into an existing tab BRR Pivot. The BRR Pivot sheet may well contain a Pivot Table and if it does, I'd like to delete it before creating a new pivot with the name BRRPivotTable.

This is my first time trying to create a pivot table on VBA so any help will be greatly appreciated. Thanks

Private Sub CommandButton1_Click()

Dim wb As Workbook
Dim BRR As Variant
Dim ADAPT As Worksheet
Dim lastrowBRR As Integer
Dim lastrowADAPT As Integer


Set wb = ThisWorkbook
Set BRR = wb.Sheets("BRR")
Set ADAPT = wb.Sheets("ADAPT")


lastrowBRR = BRR.Cells(Rows.Count, 1).Rows.Value
lastrowADAPT = ADAPT.Cells(Rows.Count, 1).Rows.Value


'PURPOSE: Creates a brand new Pivot table on a new worksheet from data in the BRR sheet


Dim pvtCache As PivotCache
Dim pvt As PivotTable
Dim StartPvt As String
Dim SrcData As String
Dim BRRpvt As PivotTable
Dim pvt2 As PivotTable


wb.Sheets("BRR Pivot").PivotTables("BRRPivotTable").Activate
On Error Resume Next
ActiveChart.Parent.Delete
On Error Resume Next


'Determine the data range you want to pivot
  SrcData = "BRR!" & Range("A1:S" & lastrowBRR).Address(ReferenceStyle:=xlR1C1)


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


'Create Pivot Cache from Source Data
  Set pvtCache = BRR.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=SrcData)
  
' add this line in case the Pivot table doesn't exit >> first time running this Macro
On Error Resume Next
Set pvt = wb.Sheets("BRR Pivot").PivotTables("BRRPivotTable") ' check if Pivot Table already created (in past runs of this Macro)


On Error GoTo 0
If pvt Is Nothing Then
    'Create Pivot table from Pivot Cache
    Set pvt = pvtCache.CreatePivotTable(TableDestination:=StartPvt, TableName:="BRRPivotTable")


Else
    ' just refresh the Pivot cache with the updated Range
    pvt.ChangePivotCache pvtCache
    pvt.RefreshTable
End If


'Add item to the Row Labels
    pvt.PivotFields("CountryName").Orientation = xlRowField
    
'Add item to the Row Labels
    pvt.PivotFields("ChannelName").Orientation = xlRowField
    
'Add item to the Row Labels
    pvt.PivotFields("Programstart").Orientation = xlRowField
    
'Add item to the Row Labels
    pvt.PivotFields("Match").Orientation = xlRowField
  
BRRpvt = BRR.PivotTables("BRRPivotTable")


'Add item to values field
    BRRpvt.AddDataField BRRpvt.PivotFields("Rate_in_Eur"), "Sum of Rate_in_Eur", xlSum
    
'Format Pivot Field
    pvt.PivotFields("Sum of Rate_in_Eur").NumberFormat = "#.##"
    
'Turn on Automatic updates/calculations --like screenupdating to speed up code
    pvt.ManualUpdate = False
    
End Sub

 

[Gerald Higgins]

I'm not a code expert, but which line of code is highlighted when the error occurs ?