If Then Statements HELP!

[natijoe]

hi all,

I'm having a slight problem here. I have a macro searching to see if
sheet exists and then copying and pasting information from it to anothe
sheet if it does exist... If it doesn't exist I have a command for
Message Box. The macro works fine until it gets to a sheet tha
doesn't exist at which point it gives me a runtime error "9" "subcrip
out of range" instead of the message box. I'm sure my code could b
much simpler and not so crowded but it's my first macro... hopefull
somebody can help me out.. Thanks a ton!
Here is an excerpt:

Sub Logger()
'
' Logger Macro
' Macro recorded 7/22/2005 by Nati Suchy
'

Sheets("Sheet1").Range("A16:B736").Copy _
Sheets("Temp Data").Range("B9")
Sheets("Sheet1").Range("C16:C736").Copy _
Sheets("RH Data").Range("C9")

' Check to see if more TRH data sheets exist


Dim wSheet As Worksheet

' Data sheet 2

Set wSheet = Sheets("Sheet2")

If wSheet Is Nothing Then 'Doesn't Exist

MsgBox "Please Save file and continue to work"
Set wSheet = Nothing

Else 'Does exist
Sheets("Sheet2").Range("B16:B736").Copy _
Sheets("Temp Data").Range("D9")
Sheets("Sheet2").Range("C16:C736").Copy _
Sheets("RH Data").Range("D9")



' Data Sheet 3


Set wSheet = Sheets("Sheet3")

If wSheet Is Nothing Then 'Doesn't Exist

MsgBox "Please Save file and continue to work"
Set wSheet = Nothing
Else 'Does exist
Sheets("Sheet3").Range("B16:B736").Copy _
Sheets("Temp Data").Range("E9")
Sheets("Sheet3").Range("C16:C736").Copy _
Sheets("RH Data").Range("E9")


' Data Sheet 4

Set wSheet = Sheets("Sheet4")

If wSheet Is Nothing Then 'Doesn't Exist

MsgBox "Please Save file and continue to work"
Set wSheet = Nothing
Else 'Does exist
Sheets("Sheet4").Range("B16:B736").Copy _
Sheets("Temp Data").Range("F9")
Sheets("Sheet4").Range("C16:C736").Copy _
Sheets("RH Data").Range("F9")


' Data Sheet 5

Set wSheet = Sheets("Sheet5")

If wSheet Is Nothing Then 'Doesn't Exist

MsgBox "Please Save file and continue to work"
Set wSheet = Nothing
Else 'Does exist
Sheets("Sheet5").Range("B16:B736").Copy _
Sheets("Temp Data").Range("G9")
Sheets("Sheet5").Range("C16:C736").Copy _
Sheets("RH Data").Range("G9")
End If
End If
End If
End If
End Su

 

[reddog9069]

You must put: On Error Resume Next

before each line like Set wsheet = Sheets("sheet2"

 

[natijoe]

Thanks a lot! Very much appreciated. It works brilliantly although it
still won't display the message box... but at least i'm not getting an
error..