Create worksheet if worksheet does not exist

[bisse2000]

Hi,
I am new to the forum, but have been using it quite a lot to learn more about VBA.
I am trying to write some code that will check a range of names within a worksheet and if there is a sheet with a name from the range excel should ignore it and move on to the next range. If no such worksheet exists it should create it. When the code encounters a blank range, it should stop the code.

I have tried several different variations, and either I can't make the "check if exist" statement to work, or I am having problems with it not working for more than one loop. After reading previous posts on this forum, I have tried with err.clear, next ws in worksheet and all types of codes but I can not make it work.

When I am running the code, it will stop on second loop at [If (Worksheets(rangename).Name <> "") Then] and give me a runtime error 9 - subscript out of range.
I am so close but have spent most of yesterday figuring out what is probably an easy solution , so now I thought I would ask the experts .
This is the code:

Sub CreateSections()

Dim i As Integer
Dim rangename As String
Dim Newsheet As String
Dim Nextrow As String

Application.ScreenUpdating = False
Sheets("Example").visible = True
Application.DisplayAlerts = False

For i = 8 To 37
rangename = Sheets("reporting dashboard").Range("A" & i)
On Error GoTo Newsheet
If (Worksheets(rangename).Name <> "") Then
GoTo Nextrow
Newsheet:
If Not rangename = "" Then
Sheets("Example").Copy before:=Sheets("example")
ActiveSheet.Name = rangename
ActiveSheet.Range("C2") = rangename
ActiveSheet.Range("C2").Select
rangename = Replace(rangename, " ", "_", 1)
ActiveWorkbook.Names.Add Name:=rangename & "_", RefersTo:=Selection
Err.Clear
End If

End If
Nextrow:
Err.Clear
Next i
Sheets("Example").visible = False
Sheets("Reporting Dashboard").Select
MsgBox "Sections Generated", vbInformation
End Sub

Thanks for your help

 

[dermie_72]

try these site.
Create sheet if sheet doesn't exist
Check if a worksheet already exists in a workbook | ExcelExperts.com
There's some real quick efficient code in this one:
Create Worksheet If It Doesn't Exist
Add Sheet If Doesn't Exist & Copy From All Worksheet
and finally this:
Conditional Check To See If A Worksheet Exists

Hope they help

 

[bisse2000]

try these site.
Create sheet if sheet doesn't exist
Check if a worksheet already exists in a workbook | ExcelExperts.com
There's some real quick efficient code in this one:
Create Worksheet If It Doesn't Exist
Add Sheet If Doesn't Exist & Copy From All Worksheet
and finally this:
Conditional Check To See If A Worksheet Exists

Hope they help

Hi, thanks for the help.
After looking a bit further into this, it turns out is was my error handling that needed some changes. Once I got that sorted the code worked.

Thanks