Sheet Exist?

[Uddinj1]

Hi All,

How can I test if a sheet exist in the activeworkbook?

I need to do the following:
If the sheet exist then ask the user if the data on that sheet should be used
for a report. If the user selects NO then delete that sheet and add a new sheet
by the same name and extract latest data into the sheet and do the report.

If the sheet does not exist then add a sheet, extract latest data and do the
report.
I can add, delete and extract new data etc. I need help to find out if the
sheet exist and avoid displaying Inputbox when there is no need. All help is
appreciated. Thanks for your help.

Kind regards

UJ

 

[Jake Marx]

Hi UJ,

A function like this should work for you:

Public Function gbDoesWSExist(rsName As String, _
Optional rwbParent As Workbook = Nothing) As Boolean
On Error Resume Next
If rwbParent Is Nothing Then Set rwbParent = ActiveWorkbook
gbDoesWSExist = Len(rwbParent.Worksheets(rsName).Name)
On Error GoTo 0
End Function


You need only pass in a workbook object if you're interested in checking the
existence of a worksheet in a workbook other than the active workbook. To
use it in your case, you would do this:

If gbDoesWSExist("MyWSName") Then
'/ ask user if it should be used
Else
'/ does not exist, add sheet, extract data
End If

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[Frank Kabel]

Hi
try something like

Dim wks as worksheet
on error resume next
set wks = worksheets("yoursheetname")
on error goto 0
if wks is nothing then
msgbox "worksheet does not exist"
else
msgbox "worksheet does exist
end if

 

[Bob Phillips]

Sub Test()
Dim oWS As Worksheet
Dim sReply As String
Dim sSheet As String

sSheet = "mySheet"
On Error Resume Next
With ActiveWorkbook
Set oWS = .Worksheets(sSheet)
If Not oWS Is Nothing Then
sReply = InputBox("Do you want to use this sheet (Yes/No)?")
If LCase(sReply) = "no" Then
Application.DisplayAlerts = False
.Worksheets(sSheet).Delete
.Worksheets.Add.Name = sSheet
End If
Else
.Worksheets.Add.Name = sSheet
End If
End With
On Error GoTo 0
'extract the data

End Sub


--

HTH

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

 

[Uddinj1]

Thnak you all for your help. This is exactly what I was looking for. Thank you.

Regards

UJ