Problems with an if (using rng Nothing) - only to produce sheet giventhat it doesn't already exist

[Wesslan]

Hi,

I am very glad for all the support I have received over the last
couple of days. I have now a problem. Given that there is already an
existing sheet (in this case "Global - " & ConditionSize & " Banks" ),
the code works smoothly (thus not producing a new sheet since it is
already there). But if the sheet is not there, I want the rng to still
be Nothing and thus it should produce a new sheet. But if the macro
can't find the sheet in question it says "Run Time Error '9':
Subscript out of range". I understand the problem encountered, I just
don't know how to get around it. I'd rather not use On Error Resume
Next.

Any suggestions?

Code:

'Copies the RawData sheet to a new sheet
If Sht2 = "Global - " & ConditionSize & " Banks" Then

Set rng = Nothing
Set rng = Worksheets(Sht2)

If rng Is Nothing Then

Sheets(Sht).Copy After:=Sheets("Assumptions")
Sheets(Sht & " (2)").Name = Sht2
End If

Else:
Sheets(Sht).Copy After:=Sheets("Assumptions")
Sheets(Sht & " (2)").Name = Sht2
End If

Regards,
Peder

 

[Nigel]

Why cannot you set the rng to nothing before you do any tests? If the sheet
exists then assign rng else do nothing.

I hope I understood your issue.

 

[Wesslan]

Nigel,

The rng is set to nothing already before the tests. Thus if no sheet
is found, there is no problem since rng = nothing.

But if there is a sheet to be found Set rng = Worksheets(Sht2)
becomes True. Thus I want it to not create a new sheet (as it already
exists). However in this case the If rng Is Nothing Then breaks down.
In my world rng is Not Nothing, so it should just proceed without
doing anything. But instead it just breaks down as rng is NOT nothing,
but rather true. weird...

Regards,

Peder

 

[Nigel]

Not sure you are setting rng before test? Your code.....

If Sht2 = "Global - " & ConditionSize & " Banks" Then
Set rng = Nothing ' set to nothing after test!
Set rng = Worksheets(Sht2)
If rng Is Nothing Then
Sheets(Sht).Copy After:=Sheets("Assumptions")
Sheets(Sht & " (2)").Name = Sht2
End If
Else:
Sheets(Sht).Copy After:=Sheets("Assumptions")
Sheets(Sht & " (2)").Name = Sht2
End If

would not this work .....

Set rng = Nothing
If Sht2 = "Global - " & ConditionSize & " Banks" Then
Set rng = Worksheets(Sht2)
Else
Sheets(Sht).Copy After:=Sheets("Assumptions")
Sheets(Sht & " (2)").Name = Sht2
End If

 

[paul.robinson]

Hi
you can test existence with this

'Tests to see if a worksheet with the given name exists in the active
workbook
Function IsSheetThere(shName As String) As Boolean
Dim DummyWks As String
IsSheetThere = False
On Error Resume Next
DummyWks = ActiveWorkbook.Worksheets(shName).Name
If Err.Number = 0 Then IsSheetThere = True
End Function

and use it as

If not isSheetThere(ShtName) then
Set wsNew =
ActiveWorkbook.Worksheets.Add(after:=Worksheets(OtherSheet) ) 'Makes
wsNew the active sheet
wsNew.Name = ShtName
end if

regards
Paul

 

[Wesslan]

Paul and Nigel,

I don't know what I would do without you guys! Now the macro runs like
a kitten and the most beautiful thing is for each day you program
some, you learn new ways to tackle problems and it all becomes easier.
But perhaps more interresting is the fact that it just becomes more
and more fun as you realize new things that should be possible...

Thanks both of you!

Sincerly,

Peder

 

[Chip Pearson]

Just FYI, I would recommend you use a different variable name than 'rng' for
a Worksheet object. 'Rng' is just to close to Range and down the road, when
you or someone else has to modify/enhance/debug the code, the name will
cause confusion.


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)