Run-time error when trying to work with different sheet

T

TBA

Excel 97
Windows 2k Pro

I have a command button on sheet2 that needs to do some "stuff" on sheet1
and then copy said "stuff" back onto sheet2. This is a stripped down
example of code for my command button on sheet2:

Private Sub cmd_RankBest_Click()
Worksheets("Sheet1").Select
Range("A1:Q36").Select 'selecting a range on sheet1
End Sub

But, I'm getting a run-time error '1004':
Select method of Range class failed

Is my code flawed?

Any insights greatly appreciated.

-gk-
 
S

Serge

Try either Worksheets("Sheet1").Range("A1:Q36").Copy/Paste/Select (whatever
Range method you need)
Or Range("Sheet!A1:Q36").Copy/Paste/Select (whatever Range method you need)

In these cases I like to use VB's WITH to save the time
for example

With ActiveWorkbook.Worksheets("Sheet1")
.Range("A1:Q36").Select
... and so on
End With
 
J

J.E. McGimpsey

With XL97, you need to set the TakeFocusOnClick property of the
command button to False.
 
S

steve

TBA,

Seems Excel wants the sheet specified... this works

Private Sub cmd_RankBest_Click()
Worksheets("Sheet1").Select
Worksheets("Sheet1").Range("A1:Q36").Select 'selecting a range on
sheet1
End Sub
 
T

TBA

steve said:
TBA,

Seems Excel wants the sheet specified... this works

Private Sub cmd_RankBest_Click()
Worksheets("Sheet1").Select
Worksheets("Sheet1").Range("A1:Q36").Select 'selecting a range on
sheet1
End Sub
Click to expand...

Thanks Steve! You were on the right track. After Googling this morning I
got a tip where I used

Worksheets("Sheet1").Select
ActiveSheet.Range("A1:Q36").Select 'selecting a range on sheet1

Basically you were right about Excel wanting the sheet specified. Oh I love
this stuff!

;)

-gk-
 
S

steve

Yep,

I just got finished doing some code and went round and round on some
similar issues until I got it right...

F&F (fun and frustrating).

Keep on Exceling!
 
Top