paste method of worksheet class failed

[Guest]

Hello, I have a program that works fine in XL2000 but in XL2003 I get a
Runtime error 1004 - Paste method of worksheet class failed. When I look at
the spreadsheet the data pasted ok.

Here is my code:
Range(Cells("1", "A"), Cells(a, "B")).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("Tables").Select
Range("A1").Select
ActiveSheet.Paste Destination:=Range("A1")

What I have done before this is perfomed a subtotal and minimized to show
just the totals. I then want to copy this and paste the totals on a new
sheet. It fails with just activesheet.paste and with the above. What is
causing this error and how can I get it fixed.

Dwaine

 

[Ron de Bruin]

Hi Dwaine

Use it like this without selecting

Range(Cells("1", "A"), Cells(a, "B")).SpecialCells(xlCellTypeVisible).Copy _
Sheets("Tables").Range("A1")

 

[Guest]

Now I get a copy method of worksheet class failed.

 

[Ron de Bruin]

Hi

Where did you copy the macro and how you set a

 

[Guest]

Here is the previous lines of the code:

Sheets("Priority Sort Totals").Select
Range("A2").Select
x = Range("A2").Value

While x <> "Grand Count"
x = Range(Cells(irow, "A"), Cells(irow, "A")).Value
irow = irow + 1
Wend

irow = irow - 1
a = irow
Range(Cells("1", "A"), Cells(a,
"B")).SpecialCells(xlCellTypeVisible).Copy _
Sheets("Tables").Range("A1")

 

[Ron de Bruin]

Hi Dwaine

This is working
I use Find in this example(faster)

Sub Find_First()
Dim Rng As Range

Sheets("Priority Sort Totals").Select
Set Rng = Range("A:A").Find(What:="Grand Count", _
After:=Range("A" & Rows.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
a = Rng.Row
Range(Cells("1", "A"), Cells(a, "B")).SpecialCells(xlCellTypeVisible).Copy _
Sheets("Tables").Range("A1")
Else
MsgBox "Nothing found"
End If
End Sub

 

[Guest]

Still fails on me. The wierd thing is that if I debug and then click on the
tables sheet and then go back to the code and continue it finishes out the
macro.

 

[Ron de Bruin]

Send me the workbook private if you want.
I look at it then for you