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

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?

Make a new sheet if it doesn't exist
 

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


"PaulW" wrote in message
...
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?

Make a new sheet if it doesn't exist
 

Cheers, this is working great!

"Jon Peltier" wrote:

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


"PaulW" wrote in message
...
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?