Run-time error when trying to work with different sheet

  • Thread starter Thread starter TBA
  • Start date Start date
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-
 
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
 
With XL97, you need to set the TakeFocusOnClick property of the
command button to False.
 
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
 
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-
 
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!
 
Back
Top