Run-time error when trying to work with different sheet

[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-

 

[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.E. McGimpsey]

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

 

[TBA]

Already did that, doesn't help.

-gk-

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

 

[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

 

[TBA]

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

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-

 

[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!