Run time 1004 error

zcahlmc

New Member
Joined
Aug 5, 2019
Messages
5
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

Code:
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
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
I'm not a code expert, but which line of code is highlighted when the error occurs ?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,744
Messages
5,626,621
Members
416,195
Latest member
tonmcg

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top