Help in Excel VBA Programing - How to check an Excel Worksheet?

[wmcray]

I am very new to Excel VBA programming.

I want to write a sample command to check if there is an existing Exce
worksheet, say for example called "Report".

If there is an existing worksheet, then it should be deleted
Otherwise, a new worksheet called "Report" should be created.

Here is the program code. Can anyone please help me with the firs
line???

If ???? (there is a worksheet called "report") Then
Sheets("Report").Delete
Else
Sheets.Add
ActiveSheet.Name = "Report"
End If

Thanks!

 

[Bob Phillips]

Here is a simple function to do it


'-----------------------------------------------------------------
Function SheetExists(Sh As String, _
Optional wb As Workbook) As Boolean
'-----------------------------------------------------------------
Dim oWs As Worksheet
If wb Is Nothing Then Set wb = ActiveWorkbook
On Error Resume Next
SheetExists = CBool(Not wb.Worksheets(Sh) Is Nothing)
On Error GoTo 0
End Function

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[wmcray]

Hi Bob,

Thanks for your prompt reply.

Please forgive me as I really do not have a lot of programmin
knowledge in Excel VBA.

But I really want to write a simple check to see if there is
worksheet titled "Report". If there is one, please delete th
worksheet, otherwise, add a new one called "Report'.

Therefore, I have figured out the IF, THEN, ElSE statement.

But I don't understand your function call such as where to put th
function and how to incorporate it into my IF, THEN, ELSE statement.

Can you simply provide me a IF statement incorporating the checkin
whether a worksheet titled "Report" exists?

Please kindly advise. Thank

 

[Bob Phillips]

Are you talking about a worksheet function? If so, you are out of luck as
you cannot do that from Excel directly, it has to be from within a macro.

If in VBA this will do it

'-----------------------------------------------------------------
Function SheetExists(Sh As String, _
Optional wb As Workbook) As Boolean
'-----------------------------------------------------------------
Dim oWs As Worksheet
If wb Is Nothing Then Set wb = ActiveWorkbook
On Error Resume Next
SheetExists = CBool(Not wb.Worksheets(Sh) Is Nothing)
If SheetExists Then
Application.DisplayAlerts = False
wb.Worksheets(Sh).Delete
Else
wb.Worksheets.Add.Name = Sh
End If
On Error GoTo 0
End Function


invoked from VBA with a simple

SheetExists "Report"

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)