CODEWITHSUNDEEP
vbaexcelpivotpivot-table
Julie Routley
Julie Routley

Reputation: 1

How can I create VBA which creates a pivot table only if it doesn't already exist

I am just learning VBA so not completely familiar with it yet.

I would like to creates a pivot table with a dynamic range only if the pivot table doesnt already exist. If the table already exists then the pivot jut needs to refresh.

so far I have this:

Sub CreatingPivot()
Dim PCache As PivotCache, LastRow As Long, pt As PivotTable
    Dim ws As Worksheet

    On Error Resume Next
    Set ws = Sheets("Pivot")
    If Err.Number <> 0 Then
        Worksheets("Sheet1").Activate
 Set PCache = ActiveWorkbook.PivotCaches.Create(SourceType:=1, SourceData:=Range("A1").CurrentRegion.Address)
     Worksheets.Add
    ActiveSheet.Name = "Pivot"
    ActiveWindow.DisplayGridlines = False
 Set pt = ActiveSheet.PivotTables.Add(PivotCache:=PCache, TableDestination:=Range("A1"), TableName:="PivotTable1")

    Else
       Sheets("Pivot").RefreshTable
    End If
End Sub

Upvotes: 0

Views: 852

Answers (1)

barryleajo
barryleajo

Reputation: 1952

Try something like:

Dim PivTbl as PivotTable

On Error Resume Next
    Set PivTbl = Sheets("Pivot").PivotTables("PivotTable1")
On Error Goto 0
    If PivTbl Is Nothing Then
        'Create pivot table
    End If
'Do your stuff

EDIT FOR OP COMMENT/FOLLOW-ON Q

Refresh the PivotTable not the Worksheet

Dim PivTbl As PivotTable

    Set PivTbl = Sheets("Pivot").PivotTables("PivotTable1")

    PivTbl.RefreshTable

Upvotes: 1

Related Questions