Application defined or object defined error vba pivot table

Question

Respected Expert, I am trying to execute below VBA code for Define my data and refresh the Pivot Table but I Am getting defined or object defined error-1004. Please guide me where I am doing mistake on below.

Sub Pivot()

Dim shPivot As Worksheet
Dim shData As Worksheet
Dim lr As Long
Dim lc As Long
Dim rng As Range

Set shPivot = ActiveWorkbook.Sheets("Data")
Set shData = ActiveWorkbook.Sheets("Pivot")

lr = shPivot.Range("A" & Rows.Count).End(xlUp).Row

Set rng = shPivot.Range(Cells(1, 1), Cells(lr, 32))


With shData.PivotTables("PivotTable1").PivotCache
        .SourceData = rng.Address(True, True, xlR1C1, True)
        .Refresh
End With

End Sub

It generally helps if you tell us where the error occurs! You need to use: Set rng = shPivot.Range(shPivot.Cells(1, 1), shPivot.Cells(lr, 32)) — Rory
Following @Rory also for lr = shPivot.Range("A" & shPivot.Rows.Count).End(xlUp).Row — Shai Rado
I am getting error on .SourceData = rng.Address(True, True, xlR1C1, True) — sagar

Shai Rado Shai Rado · Accepted Answer · 2017-03-28T07:22:47

Try the code below:

Option Explicit

Sub Pivot()

Dim shPivot As Worksheet
Dim shData As Worksheet
Dim PT As PivotTable
Dim PTCache As PivotCache
Dim lr As Long
Dim lc As Long
Dim Rng As Range

Set shPivot = ActiveWorkbook.Sheets("Data")
Set shData = ActiveWorkbook.Sheets("Pivot")

With shPivot
    lr = .Range("A" & .Rows.Count).End(xlUp).Row
    Set Rng = .Range(.Cells(1, 1), .Cells(lr, 32))
End With

' set the Pivot Cache with the updated Data Source
' Set PTCache = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Rng)

' === Option 2 : set the Pivot Cache with the updated Data Source ===
Set PTCache = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=shPivot.Name & "!" & Rng.Address(True, True, xlR1C1, False))

' Set the Pivot Table to existing "PivotTable1" in "Data" sheet
Set PT = shData.PivotTables("PivotTable1")

With PT
    .ChangePivotCache PTCache
    .RefreshTable
End With

End Sub

Application defined or object defined error vba pivot table

1 Answers