.PivotCache.SourceData causing Application or object defined error

Question

I have written the following code to edit the range of a two pivot tables. Up until the line of stars my code functions properly, however I get a "Run-time error '1004': Application-defined or object-defined" error on the .PivotCache.SourceData = rng.Address(True, True, xlR1C1, True) line. I have no idea what the cause of the error is as I copied the code from above and only changed the sheet name and pivot table name (they both definitely exist in my file). Any help?

Dim RowCount As Integer
Dim ColCount As Integer
Dim rng As Range
Dim CurrentPeriod As String
Dim PivotList As Variant
Dim Piv As String
Dim PivotSht As String
Dim PivotNme As String

RowCount = WorksheetFunction.CountA(Sheets("Data").Range("A:A"))
ColCount = WorksheetFunction.CountA(Sheets("Data").Range("1:1"))

Set rng = Sheets("Data").Range(Sheets("Data").Cells(1, 1), Sheets("Data").Cells(RowCount, ColCount))

CurrentPeriod = Sheets("Static").Range("CurrentPeriod")

With Sheets("Val Cat Current Returns (Adj)").PivotTables("CatCurrentPivot1")
    .PivotCache.SourceData = rng.Address(True, True, xlR1C1, True)
    .PivotFields("Period_id").CurrentPage = CurrentPeriod
    .PivotCache.Refresh
End With

**********************************
With Sheets("Val Cat Trend Returns (Adj)").PivotTables("CatTrendPivot1")
    .PivotCache.SourceData = rng.Address(True, True, xlR1C1, True)
    .PivotCache.Refresh
End With

A shot in the dark.. try this... `.PivotCache.SourceData = Sheets("Data").Name & "!" & rng.Address(True, True, xlR1C1, True)` — Siddharth Rout, Sep 09 '15 at 15:45
Thanks for the suggestion, but still getting the same error. — user3737057, Sep 09 '15 at 15:50
I tested the code and your code works for me. Though I am not in favor of how you are finding the last row and last column. `Sheets("Data").Cells(RowCount, ColCount)`. If there is a blank cell in Col A then your row count will go for a toss. I would recommend using [This](http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba) to find the last row and last column to construct your range — Siddharth Rout, Sep 09 '15 at 16:02

score 0 · Answer 1 · answered Sep 10 '15 at 08:50

After hours of investigation I couldn't work out what was causing the error in my file. I ended up using an alternative to .PivotCache.SourceData = rng.Address(True, True, xlR1C1, True) which is Sheets(PivotSht).PivotTableWizard SourceType:=xlDatabase, SourceData:=rng. In order to use this approach you must have a cell within the chosen pivot table selected. To get around this you can locate the top left cell of the pivot table using Sheets(PivotSht).PivotTables(PivotNme).TableRange2.Cells(1).Address. My full code is below, it references the "Static" sheet, which is where all of my pivot table names and sheets are listed. (Also see the comment from Siddharth Rout for a better way to find the range of data).

Sub CurrentPeriodPivots()

Dim RowCount As Integer
Dim ColCount As Integer
Dim rng As Range
Dim CurrentPeriod As String
Dim PivotList As Variant
Dim Piv As String
Dim PivotSht As String
Dim PivotNme As String
Dim PivotLoc As String

RowCount = WorksheetFunction.CountA(Sheets("Data").Range("A:A"))
ColCount = WorksheetFunction.CountA(Sheets("Data").Range("1:1"))

Set rng = Sheets("Data").Range(Sheets("Data").Cells(1, 1), Sheets("Data").Cells(RowCount, ColCount))

CurrentPeriod = Sheets("Static").Range("CurrentPeriod")

For i = 8 To 9
    PivotSht = Sheets("Static").Cells(i, 1).Value
    PivotNme = Sheets("Static").Cells(i, 2).Value
    PivotLoc = Sheets(PivotSht).PivotTables(PivotNme).TableRange2.Cells(1).Address
    Sheets(PivotSht).Select
    Range(PivotLoc).Select
    Sheets(PivotSht).PivotTableWizard SourceType:=xlDatabase, SourceData:=rng
    Sheets(PivotSht).PivotTables(PivotNme).PivotFields("Period_id").CurrentPage = CurrentPeriod
    Sheets(PivotSht).PivotTables(PivotNme).PivotCache.Refresh
Next i


For i = 10 To 16
    PivotSht = Sheets("Static").Cells(i, 1).Value
    PivotNme = Sheets("Static").Cells(i, 2).Value
    PivotLoc = Sheets(PivotSht).PivotTables(PivotNme).TableRange2.Cells(1).Address
    Sheets(PivotSht).Select
    Range(PivotLoc).Select
    Sheets(PivotSht).PivotTableWizard SourceType:=xlDatabase, SourceData:=rng
    Sheets(PivotSht).PivotTables(PivotNme).PivotCache.Refresh
Next i


End Sub

.PivotCache.SourceData causing Application or object defined error

1 Answers1