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

P

PaulW

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?
 
J

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/
_______
 
P

PaulW

Cheers, this is working great!

Jon Peltier said:
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/
_______
Click to expand...
 
Top