Problem in refreshing pivot table

[gio123bg]

Hi All,
If I clean and add new data in source area of the via VBA macro, I have problems if I run the refersh of the pivot table manually or using this VBA code

Set pt = Sheet4.PivotTables("PivotTable1")

pt.RefreshTable

If I use this VBA code

Dim lX As Long

For lX = 1 To Worksheets("Pivot").PivotTables.Count
Sheet4.PivotTables(lX).ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Dettaglio!" & Sheet1.Range("A1").CurrentRegion.Address(ReferenceStyle:=xlR1C1) _
, Version:=xlPivotTableVersion10)
Next

The error maessage is the following

Run-time error '438'
Object doesn't support this property or method.


Any suggestion will be well appreciated.

Thanks.

Regards,

Giovanni

 

[Jerry Sullivan]

Hi Giovanni,

The syntax of your code looks okay to me, so the problem might be in the specifics of your current workbook.

Are you getting that error message when running the first snippet with pt.RefreshTable, or the second snippet that changes the datasource?

Here's a few things you might try:

1. Eliminate the Parameter: "Version:=xlPivotTableVersion10" . It's not necessary and might cause an error if it represents a change in Version.

2. In the Immediate Window, evaluate: ?"Dettaglio!" & Sheet1.Range("A1").CurrentRegion.Address(ReferenceStyle:=xlR1C1)
Take the resulting string and see if you can manually change the Data Source using Change Data Source on the Ribbon and Copy-Pasting this string.

3. If #2 doesn't work, check if the address refers to a data source that would be valid (no missing headers, at least 2 rows of data).

4. Try stepping through the code in the VBE using F8. It's possible that the code is updating 1 or more PivotTables then hitting the error on a Pivot that has some exception (for example, a source type other than xlDatabase).

Lastly, you might consider being consistent in how you reference sheets. The code sometimes uses the Sheet Name: Worksheets("Pivot") and sometimes uses the Code Name: Sheet4. I don't think this is the cause of your error, but that might make it harder to spot incorrect references.

 

[gio123bg]

Hi Jerry,
Thank You for your suggestion.

I have changed the VBA Code in this way and now all is ok.

ref = "Dettaglio!R1C1:R" & maxrows & "C7"
Sheet4.Select
Sheet4.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
ref
ActiveWorkbook.ShowPivotTableFieldList = False
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

where maxrows is the number of rows of the input area.

Thanks again.

Regards,

Giovanni