Excel VBA Runtime error 1004 "Select method of Range class failed"

[bobkaku]

I'm trying perform the following with a VBA script in Excel 2000:

1. Unhide a hidden worksheet.
2. Copy a range of cells from another worksheet
3. Paste the copied selection into the worksheet that was unhidden.
4. Some simple formatting steps in the unhidden worksheet

But the script is failing when I tried to Paste.

Here's the code:

Private Sub cmdZoomProgSumm_click()
Sheets("Project Summary").Visible = True
Sheets("6-Blocker").Select
Range("B5:R33").Select
Application.CutCopyMode = False
Selection.Copy
Range("B5:R36").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWorkbook.Sheets("Project Summary").Select
Range("A1").Select
Selection.Paste
Columns("A:A").ColumnWidth = 5.29
Columns("A:P").Select
ActiveWindow.SmallScroll ToRight:=6
Columns("A:Q").Select
Selection.ColumnWidth = 4.71
ActiveWindow.SmallScroll ToRight:=-6
End Sub

I'm getting the error 1004 "Select method of Range class failed"
where I specify Range("A1").Select

The odd thing is that when I ran the code as a recorded macro, it
worked fine. However when I copied the code to a VBA button control, I
got the 1004 error.

Can somebody explain why I'm getting this error on the button control
and what I can do to solve this problem?

Thank you in advance.

[bpsdg]

"bobkaku" <bob...@yahoo.com> schreef in bericht
news:1193793758.3...@y42g2000hsy.googlegroups.com...

I'm not sure, because it's not so simple to try your code without having the
actual Excel file.
But I think you shouldn't select the sheets, but activate them. I think you
cannot select ranges on an inactive sheet. I think select will select the
sheet as a range. Activate is actually the same as a manual click on a sheet
tab. The first sheet seems to cause no problem because it is probably
already the active sheet.

So:

Sheets("6-Blocker").Select
ActiveWorkbook.Sheets("Project Summary").Select

Becomes:

Sheets("6-Blocker").Activate
ActiveWorkbook.Sheets("Project Summary").Activate