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.

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

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.

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.