Problem with If-Then-Else Statement
 

What is wrong with this code:
If Not Sheets("Harrison 1") Is Nothing Then
Sheets("Harrison 1").Select
Range("A3:C3").Select
Selection.Copy
Windows("DAY AHEAD LMP SHEET.xls").Activate
Range("B4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("Day Ahead Obligation.xls").Activate
Range("B6:C29").Select
Application.CutCopyMode = False
Selection.Copy
Windows("DAY AHEAD LMP SHEET.xls").Activate
Range("C7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Else
Windows("DAY AHEAD LMP SHEET.xls").Activate
Range("C7:D30").Select
Selection.ClearContents
End If

If I run this on a spreadsheet where there is no "Harrison 1" sheet, I
receive a runtime error 9 "subscript out of range" on the "If Not
Sheets("Harrison 1") Is Nothing Then" statement.

Problem with If-Then-Else Statement
 

If sheets("harrison 1") doesn't exist, then the reference to Sheets("harrison
1") will cause an error before the "is nothing" is tested.

I like to use something like:

Dim TestWks as worksheet

set testwks = nothing
on error resume next
set testwks = worksheets("harrison 1")
on error goto 0

if testwks is nothing then
'doesn't exist
else
'do the work
end if



DOOGIE wrote:

What is wrong with this code:
If Not Sheets("Harrison 1") Is Nothing Then
Sheets("Harrison 1").Select
Range("A3:C3").Select
Selection.Copy
Windows("DAY AHEAD LMP SHEET.xls").Activate
Range("B4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("Day Ahead Obligation.xls").Activate
Range("B6:C29").Select
Application.CutCopyMode = False
Selection.Copy
Windows("DAY AHEAD LMP SHEET.xls").Activate
Range("C7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Else
Windows("DAY AHEAD LMP SHEET.xls").Activate
Range("C7:D30").Select
Selection.ClearContents
End If

If I run this on a spreadsheet where there is no "Harrison 1" sheet, I
receive a runtime error 9 "subscript out of range" on the "If Not
Sheets("Harrison 1") Is Nothing Then" statement.

--

Dave Peterson

Problem with If-Then-Else Statement
 

Try this

On Error Resume Next
Set sh = Sheets("Harrison 1")
On Error Goto 0
If Not sh Is Nothing Then

...


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"DOOGIE" wrote in message
...
What is wrong with this code:
If Not Sheets("Harrison 1") Is Nothing Then
Sheets("Harrison 1").Select
Range("A3:C3").Select
Selection.Copy
Windows("DAY AHEAD LMP SHEET.xls").Activate
Range("B4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("Day Ahead Obligation.xls").Activate
Range("B6:C29").Select
Application.CutCopyMode = False
Selection.Copy
Windows("DAY AHEAD LMP SHEET.xls").Activate
Range("C7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Else
Windows("DAY AHEAD LMP SHEET.xls").Activate
Range("C7:D30").Select
Selection.ClearContents
End If

If I run this on a spreadsheet where there is no "Harrison 1" sheet, I
receive a runtime error 9 "subscript out of range" on the "If Not
Sheets("Harrison 1") Is Nothing Then" statement.

Problem with If-Then-Else Statement
 

try it like this
Sub copyifn()
Set mywb = Workbooks("DAY AHEAD LMP SHEET.xls")
On Error GoTo next1
Sheets("joe").Range("a1").Copy
mywb.Sheets("try this").Range("h1").PasteSpecial Paste:=xlPasteValues
next1:
On Error GoTo next2
Sheets("Sheet4").Range("f2:h4").Copy
mywb.Sheets("try this").Range("h3").PasteSpecial Paste:=xlPasteValues
next2:
MsgBox "done"
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"DOOGIE" wrote in message
...
What is wrong with this code:
If Not Sheets("Harrison 1") Is Nothing Then
Sheets("Harrison 1").Select
Range("A3:C3").Select
Selection.Copy
Windows("DAY AHEAD LMP SHEET.xls").Activate
Range("B4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("Day Ahead Obligation.xls").Activate
Range("B6:C29").Select
Application.CutCopyMode = False
Selection.Copy
Windows("DAY AHEAD LMP SHEET.xls").Activate
Range("C7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Else
Windows("DAY AHEAD LMP SHEET.xls").Activate
Range("C7:D30").Select
Selection.ClearContents
End If

If I run this on a spreadsheet where there is no "Harrison 1" sheet, I
receive a runtime error 9 "subscript out of range" on the "If Not
Sheets("Harrison 1") Is Nothing Then" statement.