Dear All

I'm struggling with working out if a sheet exists in my workbook. I've used
code posted from this group but need to tweek it to create a sheet name based
on a couple of cell values. When I use the code below, Run Time Error 9 is
returned.

myShtName creates a string 'prd1 Product Name' - this is the sheet name I'm
looking for - if it exists then no action is needed, otherwise copy a
template sheet at the end of the workbook, and name the sheet 'prd1 Product
Name'.

As usual, any help gratefully appreciated

Trevor.
--------------------------------------
Dim rng As Range, cell As Range
Dim ws As Workksheet
Dim myShtName As String

Set rng = Range("productlist")

For Each cell In rng
If cell <> "" Then
myShtName = "prd" & cell.Offset(0, -1).Value & " " & cell.Value
Set ws = Worksheets(myShtName)
If ws Is Nothing Then
Sheets("Product Template").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = myShtName
Range("B3") = cell.Value
End If
End If
Next cell

Hi Trevor,

The Run Time Error 9 is returned when you try and set a reference to a
sheet that doesn't exist. That's fine but do it like this -

On Error Resume Next
Set ws = Nothing
Set ws = Worksheets(myShtName)
On Error goto 0
' or resume normal error handler
If ws is nothing then
'etc

Are you really wanting to potentially insert a sheet in each loop. If so, or
if 'ws' may already refer to a sheet, note the line set ws = Nothing before
testing.

Regards,
Peter T


"Trevor Williams" <(E-Mail Removed)> wrote in
message news:34F64848-66D9-4419-B87D-(E-Mail Removed)...
> Dear All
>
> I'm struggling with working out if a sheet exists in my workbook. I've
used
> code posted from this group but need to tweek it to create a sheet name
based
> on a couple of cell values. When I use the code below, Run Time Error 9
is
> returned.
>
> myShtName creates a string 'prd1 Product Name' - this is the sheet name
I'm
> looking for - if it exists then no action is needed, otherwise copy a
> template sheet at the end of the workbook, and name the sheet 'prd1
Product
> Name'.
>
> As usual, any help gratefully appreciated
>
> Trevor.
> --------------------------------------
> Dim rng As Range, cell As Range
> Dim ws As Workksheet
> Dim myShtName As String
>
> Set rng = Range("productlist")
>
> For Each cell In rng
> If cell <> "" Then
> myShtName = "prd" & cell.Offset(0, -1).Value & " " & cell.Value
> Set ws = Worksheets(myShtName)
> If ws Is Nothing Then
> Sheets("Product Template").Copy After:=Sheets(Sheets.Count)
> ActiveSheet.Name = myShtName
> Range("B3") = cell.Value
> End If
> End If
> Next cell
>

Hi

....
Dim ws As Object
Dim varSourceSheet As String
Dim varCont As Boolean
....
varSourceSheet = "prd1 Product Name"
For Each ws In Worksheets
varCont = IIf(varCont, True, ws.Name = varSourceSheet)
Next ws
If varCont Then
' your actions
Else
' your actions
End If
....


--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )



"Trevor Williams" <(E-Mail Removed)> wrote in
message news:34F64848-66D9-4419-B87D-(E-Mail Removed)...
> Dear All
>
> I'm struggling with working out if a sheet exists in my workbook. I've
> used
> code posted from this group but need to tweek it to create a sheet name
> based
> on a couple of cell values. When I use the code below, Run Time Error 9
> is
> returned.
>
> myShtName creates a string 'prd1 Product Name' - this is the sheet name
> I'm
> looking for - if it exists then no action is needed, otherwise copy a
> template sheet at the end of the workbook, and name the sheet 'prd1
> Product
> Name'.
>
> As usual, any help gratefully appreciated
>
> Trevor.
> --------------------------------------
> Dim rng As Range, cell As Range
> Dim ws As Workksheet
> Dim myShtName As String
>
> Set rng = Range("productlist")
>
> For Each cell In rng
> If cell <> "" Then
> myShtName = "prd" & cell.Offset(0, -1).Value & " " & cell.Value
> Set ws = Worksheets(myShtName)
> If ws Is Nothing Then
> Sheets("Product Template").Copy After:=Sheets(Sheets.Count)
> ActiveSheet.Name = myShtName
> Range("B3") = cell.Value
> End If
> End If
> Next cell
>

Brilliant, thanks Peter - works a treat!

"Peter T" wrote:

> Hi Trevor,
>
> The Run Time Error 9 is returned when you try and set a reference to a
> sheet that doesn't exist. That's fine but do it like this -
>
> On Error Resume Next
> Set ws = Nothing
> Set ws = Worksheets(myShtName)
> On Error goto 0
> ' or resume normal error handler
> If ws is nothing then
> 'etc
>
> Are you really wanting to potentially insert a sheet in each loop. If so, or
> if 'ws' may already refer to a sheet, note the line set ws = Nothing before
> testing.
>
> Regards,
> Peter T
>
>
> "Trevor Williams" <(E-Mail Removed)> wrote in
> message news:34F64848-66D9-4419-B87D-(E-Mail Removed)...
> > Dear All
> >
> > I'm struggling with working out if a sheet exists in my workbook. I've
> used
> > code posted from this group but need to tweek it to create a sheet name
> based
> > on a couple of cell values. When I use the code below, Run Time Error 9
> is
> > returned.
> >
> > myShtName creates a string 'prd1 Product Name' - this is the sheet name
> I'm
> > looking for - if it exists then no action is needed, otherwise copy a
> > template sheet at the end of the workbook, and name the sheet 'prd1
> Product
> > Name'.
> >
> > As usual, any help gratefully appreciated
> >
> > Trevor.
> > --------------------------------------
> > Dim rng As Range, cell As Range
> > Dim ws As Workksheet
> > Dim myShtName As String
> >
> > Set rng = Range("productlist")
> >
> > For Each cell In rng
> > If cell <> "" Then
> > myShtName = "prd" & cell.Offset(0, -1).Value & " " & cell.Value
> > Set ws = Worksheets(myShtName)
> > If ws Is Nothing Then
> > Sheets("Product Template").Copy After:=Sheets(Sheets.Count)
> > ActiveSheet.Name = myShtName
> > Range("B3") = cell.Value
> > End If
> > End If
> > Next cell
> >
>
>
>