Check if Sheet exists before adding new.

[shaggyforum]

Hi this is my first post here, after looking for a bit I didn't find an answer so I was hoping someone out there could help me.

I've got a macro, it needs to add a new sheet unless the sheet already exists. If it exists it should ignore trying to add otherwise add the sheet.

Sounds simple but I can't get it.

What I have is

If Sheets("Accounts") Is Nothing Then
    Worksheets.Add(After:=Worksheets(Worksheets.count)).Name = "Accounts"
 End If

But it needs an On Error Resume Next which I don't want. Seems like there should be an easy solution that doesn't require much code but is some VBA command I'm not familiar with. Thanks very much for your time.

 

[Firefly2012]

Hi

You could od it like this:

Dim ws As Worksheet

On Error Resume Next
Set ws = Worksheets("Accounts")
On Error Goto 0

If ws Is Nothing Then Worksheets.Add(After:=Worksheets(Worksheets.count)).Name = "Accounts"

 

[CodeNinja]

bsheetexists = false
for i = 1 to sheets.count
  if sheets(i).name = "Accounts" then BSheetExists = True
next i
if bsheetexists = false then
  worksheets.add(after:=worksheets(worksheets.count)).name = "Accounts"
endif

 

[shaggyforum]

bsheetexists = false
for i = 1 to sheets.count
  if sheets(i).name = "Accounts" then BSheetExists = True
next i
if bsheetexists = false then
  worksheets.add(after:=worksheets(worksheets.count)).name = "Accounts"
endif

Thanks very much, that worked great.