1

I am looking for your assistance with the following error.

I have some VBA code that opens a workbook and copies in a sheet containing a pivot table.

The code then selects the pivot table, filters by date, and copies some fields out.

This functionality works perfectly on first time use. However, when this report is used more than once in a day, it hits the following error:

enter image description here

The issue is with the assignment of the Pivot table name, as the VBA just uses system defaults. The code is as follows:

 Sheets("Sheet1").Select

 ActiveWorkbook.ShowPivotTableFieldList = False

 ActiveSheet.PivotTables("PivotTable1").PivotFields("Date ").AutoSort _
    xlDescending, "Date "

Is there an alternative way dynamically select the pivot table regardless of name?

Alternatively, is there code to clear the history at the end of execution so that every time the report is run it will be pivot 1?

Any help would be greatly appreciated.

Thanks in advance

RGA
  • 2,577
  • 20
  • 38
Big_Papa_B
  • 139
  • 1
  • 14

1 Answers1

2

If you know there will only be one pivot table (as it seems you have intended in your post to convey, though I may have misinterpretted this), it is better to use the index than the name, as the name will be different if the new pivot table were created while another had existed. Change your call to:

If Sheets("Sheet1").PivotTables.Count <> 0 Then
    Sheets("Sheet1").PivotTables(1).PivotFields("Date ").AutoSort xlDescending, "Date "
End If

Note I also removed the "Select" usage, as this is considered bad practice for a variety of reasons.

Community
  • 1
  • 1
RGA
  • 2,577
  • 20
  • 38