VBA: Make a new sheet if it doesn't exist

[Guest]

Since my last post is pages behind, thought I'd repost to gain further help.
Got this from my initial query (cheers Don Guillett)

Sub mynewsheets()
For Each c In Range("myrange")
On Error Resume Next
If Sheets.Name <> c Then
Sheets.Add.Name = c
End If
Next c
End Sub

I changed myrange to Range("B2:B8") where i've put some names. When I run
the macro I get "Compile Error: Method or data member not found" and .Name is
highlighted in the If Sheets.Name <> c Then row.
Am I not loading something I should be?

Can anyone help?

 

[Jon Peltier]

There seems to be a line or two missing. Try this:

Sub mynewsheets()
Dim c As Range
Dim ws As Worksheet
For Each c In Range("myrange").Cells
Set ws = Nothing
On Error Resume Next
Set ws = Worksheets(c.Value)
On Error GoTo 0
If ws Is Nothing Then
Worksheets.Add.Name = c.Value
End If
Next c
End Sub

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Guest]

Cheers, this is working great!

There seems to be a line or two missing. Try this:

Sub mynewsheets()
Dim c As Range
Dim ws As Worksheet
For Each c In Range("myrange").Cells
Set ws = Nothing
On Error Resume Next
Set ws = Worksheets(c.Value)
On Error GoTo 0
If ws Is Nothing Then
Worksheets.Add.Name = c.Value
End If
Next c
End Sub

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______