MACRO PROBLEM: How to add error handling if pivot table already exists on sheet

[cgeorge4]

Using Excel 2007 at home / Excel 2010 at work
Window 7

The code below is a recorded code that creates a pivot table on the same sheet as the data. I added and changed some lines of code where necessary with no problem.

PROBLEM: I'm not sure where to place the first "error handling" line of code - or exactly what type of error handling code it should be.

I need a message box to alert the user that a pivot table already exists on the sheet. The exact message should be as follows:

"A Supplier Pivot Table(s) already exists. Please delete and run code again."

The message box should just have a button for "Okay". Then closes.

The user will manually clear or delete the pivot tables that need to be cleared or deleted from the sheet - after that they will run the code again.

IMPORTANT: I don't want to add a code to delete all existing pivots on the sheet because there are 3 to 4 small pivots on the sheet that should not be removed.



Thanks for your help in advance Thanks!

Sub pivot()'


Dim FinalRow As Long
Dim FinalCol As Long


Application.ScreenUpdating = False
With ActiveSheet




FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
FinalCol = Cells(1, Columns.Count).End(xlToLeft).Row
    
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "PSRV!R1C2:R" & FinalRow & "C16", Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:="PSRV!R23C20", TableName:="PivotTable9", DefaultVersion _
        :=xlPivotTableVersion12
    Sheets("APSRV").Select
    Cells(24, 21).Select
    ActiveSheet.PivotTables("PivotTable9").AddDataField ActiveSheet.PivotTables( _
        "PivotTable9").PivotFields("Site Code"), "Count of Site", xlCount
    With ActiveSheet.PivotTables("PivotTable9").PivotFields("Usual vs Open")
        .Orientation = xlPageField
        .Position = 1
    End With
    ActiveWorkbook.ShowPivotTableFieldList = False
    ActiveSheet.PivotTables("PivotTable9").PivotFields("Usual vs Open"). _
        CurrentPage = "(All)"
    With ActiveSheet.PivotTables("PivotTable9").PivotFields("Usual vs Open")
        .PivotItems("Open").Visible = True
        .PivotItems("Rented").Visible = False
    End With
    ActiveSheet.PivotTables("PivotTable9").PivotFields("Usual vs Open"). _
        EnableMultiplePageItems = True
    ActiveSheet.PivotTables("PivotTable9").TableStyle2 = "PivotStyleLight1"
    
    End With
    ActiveWorkbook.ShowPivotTableFieldList = False
    With ActiveSheet.PivotTables("PivotTable9").PivotFields("Site")
        .Orientation = xlRowField
        .Position = 1
  
    End With
    
    
Application.CutCopyMode = False
Application.ScreenUpdating = True




End Sub

 

[AlphaFrog]

Put something like this at the start of your macro to detect if sheet APSRV already has a pivot table called PivotTable9. You may want to use a more unique pivot table name within the code.

[COLOR=darkblue]Sub[/COLOR] pivot()
    
[COLOR=darkblue]Dim[/COLOR] FinalRow [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
[COLOR=darkblue]Dim[/COLOR] FinalCol [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
[B][COLOR=darkblue]Dim[/COLOR] pt [COLOR=darkblue]As[/COLOR] PivotTable[/B]

[B]    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR][/B]
[B]    [COLOR=darkblue]Set[/COLOR] pt = Sheets("APSRV").PivotTables("PivotTable9")[/B]
[B]    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] 0[/B]
[B]    [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] pt [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR][/B]
[B]        MsgBox "A Supplier Pivot Table(s) already exists. Please delete and run code again.", vbExclamation, "Pivot Table Exists"[/B]
[B]        [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR][/B]
[B]    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR][/B]
    [COLOR=green]'[/COLOR]
    [COLOR=green]'[/COLOR]
    [COLOR=green]'[/COLOR]

 

[cgeorge4]

Thank you Alpha Frog, your err handling code works perfectly. I added it just before the section of my code that creates my pivot table. The message box appears, but the VBA window appears after the "Okay" button is clicked. Not a good thing for other users to see


I actually tried adding it directly after the section of code that creates the pivot as well - but that stopped the pivot from being created at all.

So, the error is definitely mine.

Please let me know where I should add it. I pasted my code below. If you could insert the err handler where it should go - I would be so happy and extremely grateful. Not being lazy, just so tired

Dim PT As PivotTable [COLOR=#a52a2a](was inserted here)[/COLOR]FinalRow = Cells(Rows.Count, 1).End(xlUp).RowFinalCol = Cells(1, Columns.Count).End(xlToLeft).Row        ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _        "PSRV!R1C2:R" & FinalRow & "C16", Version:=xlPivotTableVersion12).CreatePivotTable _        TableDestination:="PSRV!R23C20", TableName:="PivotTable9", DefaultVersion _        :=xlPivotTableVersion12    Sheets("APSRV").Select    Cells(24, 21).Select    ActiveSheet.PivotTables("PivotTable9").AddDataField ActiveSheet.PivotTables( _        "PivotTable9").PivotFields("Site Code"), "Count of Site", xlCount    With ActiveSheet.PivotTables("PivotTable9").PivotFields("Usual vs Open")        .Orientation = xlPageField        .Position = 1 
[COLOR=#574123]    End With[/COLOR]

 

[cgeorge4]

Alpha Frog, I need to apologize. I knew what to do without even thinking.....sorry.
I've been working on projects all weekend and it is sooooo hot here...and I'm pooped

I added half of your error handling code to the beginning of mine - and the rest of it to the bottom. As I should have done before.
And I did use a generic pivot table name as you suggested.

Everything works perfectly! Here is what my code looks like now (in case anyone needs to see this)........AND THANK YOU!! {SOLVED}

Additional Note: I'll be adding this code to my existing macro toolbar add-in so other users won't see the VBA module by accident or on purpose

Dim FinalRow As Long
Dim FinalCol As Long
Dim PT As PivotTable


 On Error Resume Next
    Set PT = Sheets("PSRV").PivotTables("PivotTablePSRV1")
    


Application.ScreenUpdating = False
With ActiveSheeT

(The main part of my code goes here)

End With
        
Application.CutCopyMode = False
Application.ScreenUpdating = True




On Error GoTo 0
    If Not PT Is Nothing Then
        MsgBox "A Supplier Pivot Table(s) already exists. Please delete and run code again.", vbExclamation, "Pivot Table Exists"
        Exit Sub
    End If




End Sub

 

[AlphaFrog]

If it works then great, but that's not how it was supposed to be used. Its all supposed to go at the start of the macro. I suspect there is another error in your original code, but I don't know what.

 

[cgeorge4]

Hmmmm....I will definitely consider that possibility.

I've deleted and added rows and columns to the data to make sure my code works all the time - and it does.
I've run the code several times making mistakes that another user might make - and the error handler works all the time.

I'll be using the same err handler on another project and I'll add it at the start of it - and see what happens.

Thank you again....and again!


Hey I don't know how to mark this thread as SOLVED. I'm looking everywhere

 

[AlphaFrog]

You're welcome. If it works, it works. That's what counts.

There is no "Solved" marker.

 

[cgeorge4]

Thank you! You are absolutely correct!