Run-time error '1004': Select method of Range class failed

[pglufkin]

This simple code breaks on line 7 with the above error message. I cannot for
the life of me figure out why. Can anyone help? Thanx in advance

1 Private Sub Worksheet_Change(ByVal Target As Range)
2 If Target.Address = "$E$10" Then
3 Application.ScreenUpdating = False
4 Select Case Target.Value
5 Case "N/A"
6 Sheets("QBQuery1_1Criteria").Select
7 Range("O1:O530").Select
Selection.Copy
Range("K1").Select
ActiveSheet.Paste
Case "All Projects Actual"
Sheets("QBQuery1_1Criteria").Select
Range("P1530").Select
Selection.Copy
Range("K1").Select
ActiveSheet.Paste
Application.ScreenUpdating = True
End Select
Sheets("Residential_Estimator").Select
End If
End Sub

 

[Dave Peterson]

When code is in a General module, then the unqualified range will refer to the
activesheet. But in code that is behind a worksheet, any unqualified ranges
will refer to the sheet with the code.

So say this code is behind "sheet1"

Sheets("QBQuery1_1Criteria").Select
Range("O1:O530").Select

This is equivalent to:

Sheets("QBQuery1_1Criteria").Select
sheets("sheet1").Range("O1:O530").Select

And you can only select a range on a sheet that is active.

You could use this:

Sheets("QBQuery1_1Criteria").Select
sheets("QBQuery1_1Criteria").Range("O1:O530").Select

But in general, there are better ways to do things than by selecting them:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$E$10" Then
'since we're not selecting, no reason to hide the changes
'Application.ScreenUpdating = False
Select Case Target.Value
Case "N/A"
with Sheets("QBQuery1_1Criteria")
.Range("O1:O530").Copy _
destination:=.range("K1")
end with
Case "All Projects Actual"
with Sheets("QBQuery1_1Criteria")
.Range("P1530").Copy _
destination:=.range("K1")
end with
End Select
'Application.ScreenUpdating = True
'and then there's no reason to have to select the original sheet
'Sheets("Residential_Estimator").Select
End If
End Sub

I assumed that K1 was on the same QBQuery1_1Criteria worksheet. If K1 was
supposed to be on the sheet that owned the code, change this:

with Sheets("QBQuery1_1Criteria")
.Range("P1530").Copy _
destination:=me.range("K1")
end with

(in both places. Me is the object that owns the code--in this case, the
worksheet.)

 

[pglufkin]

Dave, you nailed it! Thank you.

I need to spend some time and brush up on my theory . . . . can you
recommend a good VBA soup to nuts book which can take me from upper level
beginner to upper level intermediate?

Thanks again!

Paul

 

[Dave Peterson]

Debra Dalgleish has a list of books at her site:
http://www.contextures.com/xlbooks.html

John Walkenbach's is a nice one to start with.

See if you can find them in your local bookstore/internet site and you can
choose what one you like best.

Dave, you nailed it! Thank you.

I need to spend some time and brush up on my theory . . . . can you
recommend a good VBA soup to nuts book which can take me from upper level
beginner to upper level intermediate?

Thanks again!

Paul