Need help changing Pivot Table source data

Bagharmin

Board Regular
I have a Pivot Table that is based on data contained on another worksheet in the same workbook. The number of rows in the source data changes from time to time. I want to refresh the Pivot Table in the macro that changes the number of rows in the source data, but I keep getting an "Object doesn't support this property or method" error. Here's the code in question:

Code:
    CurrAddress = "[" & ActiveWorkbook.Name & "]Data Restructure!R2C1:R" & LastRow & "C7"

    Worksheets("Pivot").PivotTable("MyPivot").ChangePivotCache ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=CurrAddress)
Prior to the way it currently looks, I was getting an application/object defined error. I've changed it around several times, but can't seem to get it right. There is a PivotTable ("MyPivot") and a Pivot Chart on the "Pivot" worksheet that are linked together (changing the table changes the chart as well).

Thanks so much for the help. This is all I need to finish off the macro.
 

Bagharmin

Board Regular
Never mind. I changed

Code:
Worksheets("Pivot").PivotTable("MyPivot").ChangePivotCache ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=CurrAddress)
to

Code:
Worksheets("Pivot").PivotTable[COLOR="#FF0000"][/COLOR][U][/U][I][/I][B]s[/B]("MyPivot").ChangePivotCache ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=CurrAddress)
and it worked. One little letter ...
 

Some videos you may like

This Week's Hot Topics

Top