Another 'Sheet Exists' Question...

[Guest]

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

 

[Peter T]

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

 

[Arvi Laanemets]

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
....

 

[Guest]

Brilliant, thanks Peter - works a treat!

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