Check to see if Worksheet Exists

[Zeroman]

Hi all,

I want to see if a worksheet called "Surplus" exists and if it doesn'
I want to create it.

Thanks for your help in advance.

Zeroma

 

[Bob Flanagan]

dim oSheet as worksheet
set oSheet =Nothing
on error resume next
set oSheet = sheets("Surplus")
on error goto 0
if oSheet is Nothing then
Sheets.add
activesheet.name ="Surplus"
end if

Robert Flanagan
Macro Systems
Delaware, U.S. 302-234-9857
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[anilsolipuram]

try this


Public Sub t()

Dim Wkbk As Workbook
Dim wksht As Worksheet
Set Wkbk = ActiveWorkbook

Dim e As Integer
e = 0
For Each wksht In Wkbk.Worksheets
If wksht.Name = "Surplus" Then
e = 1
End If
Next
If e = 0 Then
Sheets.Add.Name = "Surplus"
End If

End Su

 

[Norman Jones]

Hi Zeroman,

Try:

Sub Tester02()

On Error Resume Next
ActiveWorkbook.Sheets.Add.Name = "Sheet4"
On Error GoTo 0

End Sub

If the sheet does not exist, the obove creates it; if the sheet already
exists, the procedure errors silently.

 

[crispbd]

The following code will do what you asked.

Sub TestExists()

Dim FoundIt As Boolean
Dim NS As Object

For Each c In ThisWorkbook.Sheets
If c.Name = "Surplus" Then FoundIt = True
Next

If FoundIt = False Then
Set NS = Sheets.Add
NS.Name = "Surplus"
End If

End Su

 

[JE McGimpsey]

one way:


Dim wsSheet As Worksheet
On Error Resume Next
Set wsSheet = Worksheets("Surplus")
On Error GoTo 0
If wsSheet is Nothing Then _
Worksheets.Add(After:=Sheets(Sheets.Count)).Name = "Surplus"

 

[Bob Phillips]

Here is one way

Dim wb As String
Dim sFilename As String

sFilename = "C:\myTest\BobsFile.xls"
On Error Resume Next
wb = Dir(sFilename)
If wb = "" Then
Workbooks.Add
ActiveWorkbook.SaveAs Filename:=sFilename
End If

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Norman Jones]

Hi Zeroman,

Please iignore my code which is fatally flawed!

 

[JE McGimpsey]

Did you test this? It doesn't do what you think it does...

 

[Norman Jones]

Hi JE.

Indeed, hence my followup post.

Thank you.

 

[Bob Phillips]

sorry, I gave code for a workbook. But you have plenty of other replies.

--

HTH

RP
(remove nothere from the email address if mailing direct)

Here is one way

Dim wb As String
Dim sFilename As String

sFilename = "C:\myTest\BobsFile.xls"
On Error Resume Next
wb = Dir(sFilename)
If wb = "" Then
Workbooks.Add
ActiveWorkbook.SaveAs Filename:=sFilename
End If

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Alan Beban]

Hi all,

I want to see if a worksheet called "Surplus" exists and if it doesn't
I want to create it.

Thanks for your help in advance.

Zeroman

If your workbook has a reference to Microsoft Scripting Runtime

Sub qwerty1()
Dim x As Dictionary, ws As Worksheet
Set x = New Dictionary
For Each ws In Sheets
x.Add CStr(ws.Name), ws.Name
Next
If Not x.Exists("Surplus") Then _
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Surplus"
End Sub

Alan Beban

 

[Zeroman]

Wow 11 replies.

Thanks for all the help, I will get back to you and tell you if i
works