Refinement of VBA

[Access Beginner]

Hi All,

I have the following code that severs the data connection to a couple of pivot tables on a couple of sheets. Is there a way to make the below code more compact/efficient?

Sheets("DebtSummaryIII").Select
    ActiveSheet.PivotTables("PivotTable2").SaveData = False
    ActiveWorkbook.ShowPivotTableFieldList = False
    
    Sheets("DebtSummaryII").Select
    ActiveSheet.PivotTables("PivotTable1").SaveData = False
    ActiveWorkbook.ShowPivotTableFieldList = False
    
    Sheets("DebtSummaryII").Select
    ActiveSheet.PivotTables("PivotTable2").SaveData = False
    ActiveWorkbook.ShowPivotTableFieldList = False
    
    Sheets("DebtPivotTable").Select
    ActiveSheet.PivotTables("DebtPivotTable").SaveData = False
    ActiveWorkbook.ShowPivotTableFieldList = False
    
    Sheets("DebtPivotTable").Select
    ActiveSheet.PivotTables("PivotTable1").SaveData = False
    ActiveWorkbook.ShowPivotTableFieldList = False

Using Excel 2013

Cheers
Haydn

 

[Smitty]

You could try something like this:

    For i = 1 To Sheets("DebtSummaryIII").PivotTables.Count
        Sheets("DebtSummaryIII").PivotTables(i).SaveData = False
    Next i

And you only need this once: ActiveWorkbook.ShowPivotTableFieldList = False

HTH,

 

[Access Beginner]

Thanks Smitty,

Is there also a way to delete every pivot table in a Workbook, without the need to reference the sheets or pivot tables individually, which basically what my original code does ( deletes the data connection to every pivot table in the workbook).

 

[Smitty]

You could loop through the sheets:

Dim ws as WorkSheet
Dim i as Long
  
  For Each ws in ActiveWorkbook.Worksheets
    For i = 1 To ws.PivotTables.Count
        ws.PivotTables(i).SaveData = False
    Next i
  Next ws

Zack Barresse has some code for deleting them all here:

http://www.mrexcel.com/forum/excel-...al-basic-applications-delete-pivot-table.html

 

[Access Beginner]

Thanks Smitty,

Worked like a charm

Cheers
Haydn