Search workbook for worksheet name, IF yes then run code, IF not display MsgBox

[crkennedy]

Hi All

I was wondering if I could get some help.
I have some code which I refreshes a pivot table connected to a button. Users are able to click the button after downloading the source data and the pivot table updates. However, if they do not copy of the source data's sheet name exactly then there is a subscript out of range error. I want the code to check the workbook if this worksheet exists first of all, if it does then run the code. If it doesn't then show a msgbox which prompts the user to change the name of the worksheet so the code will run. I have tried multiple ways of doing this, but I can't seem to get the structure or the syntax correct for the error handling/msg prompt part. Can anyone help? Is there a better way to do this?

[Jan Karel Pieterse]

Something like this:


Dim oSh as WorkSheet
On Error Resume Next
Set oSh = ActiveWorkbook.WorkSheets("TheSHeet")
On Error Goto 0
If oSh Is Nothing Then
Msgbox "Worksheet 'TheSHeet' not found!"
Else
'Do the refresh
End If

[crkennedy]

Hi Jan

Thank you so much, this works exactly the way I wanted it to! Thank you once more. I'm so pleased.