Deleting sheets and pivot tables in Excel with VBA

2
votes

I'm trying to integrate the creation of a power pivot and pivot chart in VBA and have worked out a big part. However, I am encountering a problem which I cannot seem to overcome

I create new worksheets where i create the Pivot and export it to PDF. when this is done, i delete the pivottable and worksheet with: 

Sub DeleteAllPivotTables()
    PURPOSE: Delete all other sheets and pivots
    Dim sht As Worksheet
    Dim pvt As PivotTable
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    For Each sht In ActiveWorkbook.Worksheets
        If sht.Name <> "Output" Then
            If sht.Name <> "Syntax" Then
                For Each pvt In sht.PivotTables
                    pvt.TableRange2.Clear
                Next pvt
                sht.Delete
            End If
        End If
    Next sht
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

When I delete the sheet without the part of the macro for deleting the pivot tables, I encounter a run-time 1004 error. In my knowledge because the pivotname "PivotTable1" is already taken and not deleted with the macro. When the sheet is already deleted, how can I solve this issue?

Thanks in advance!

2
vbaexcelpivot-tablepowerpivot
How about two steps. 1) loop and delete all pivot tables in the workbook (don't need to loop through sheets for this since the workbook has it's own collection of pivot table objects). 2) loop and delete all sheets. – Scott Holtzman

2 Answers

1
votes

You're not actually deleting the PivotTable when you Clear it. If you want to delete it, you need to use a solution like this or this. I've updated your code to include those solutions.

Sub DeleteAllPivotTables()
    PURPOSE: Delete all other sheets and pivots
    Dim sht As Worksheet
    Dim pvt As PivotTable
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    For Each sht In ActiveWorkbook.Worksheets
        If sht.Name <> "Output" Then
            If sht.Name <> "Syntax" Then
                For Each pvt In sht.PivotTables
                    pvt.Range(pvt.TableRange2.Address).Delete
                Next pvt
                sht.Delete
            End If
        End If
    Next sht
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

Hope it helps!

0
votes

1004 is an error that appears quite often in VBA. In your case, you are deleting Worksheets, without checking how many Sheets left do you have left.

If you only have 1 Sheet left (A Sheet is a Worksheet or a Chart) and you delete it, error 1004 comes. To avoid it, make sure that Sheets.Count is more than 1, before deleting:

Public Sub TestMe()
    Dim ws  As Worksheet
    For Each ws In Worksheets
        If Sheets.Count > 1 Then ws.Delete
    Next ws
End Sub