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
– Rory, Commented Mar 28, 2017 at 7:15
Following @Rory also for lr = shPivot.Range("A" & shPivot.Rows.Count).End(xlUp).Row — Shai Rado
– Shai Rado, Commented Mar 28, 2017 at 7:17
I am getting error on .SourceData = rng.Address(True, True, xlR1C1, True) — sagar
– sagar, Commented Mar 28, 2017 at 7:20

Shai Rado · Accepted Answer · 2017-03-28 08:21:52Z

1

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

edited Mar 28, 2017 at 8:21

answered Mar 28, 2017 at 7:22

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

CallumDA Over a year ago

Hi Shai , I think this answer could do with a bit of explanation - what was OP doing wrong? Also, With PT at the end isn't doing anything?

Shai Rado Over a year ago

@CallumDA thanks for the With PT part :) about explanation, I've added comments inside the code, from my experience with PivotTables this is the most reliable way to go

sagar Over a year ago

@ShaiRado Sir, I am getting Type mismatch error in Set PTCache = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Rng)

Shai Rado Over a year ago

@sagar if you add Debug.Print Rng.Address before this line, what are you getting in the immediate window ?

sagar Over a year ago

@ShaiRado $A$1:$AF$14999 is reflecting in the immediate window which is correct range.

|

Collectives™ on Stack Overflow

Application defined or object defined error vba pivot table

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related