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?

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" <PaulW@discussions.microsoft.com> wrote in message
news:9CD560EF-4050-424F-A8A5-3852B9E933C7@microsoft.com...
> 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?

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" <PaulW@discussions.microsoft.com> wrote in message
> news:9CD560EF-4050-424F-A8A5-3852B9E933C7@microsoft.com...
> > 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?
>
>
>