copy paste special

Swifey

Active Member
Joined
Jan 16, 2003
Messages
421
I am trying to copy a Grand Total row and paste special the values onto a worksheet called Sheet1. I get a Run-time error '424': Object Required.

My code is as follows:

Code:
With Sheets("Wessex").Range("C1:C65536")
    Set a = .Find("Grand Total", LookIn:=xlValues)
    If Not a Is Nothing Then
        firstAddress = a.Address
        Do
            a.EntireRow.copy(Sheets("Sheet1").Range("A65536").End(xlUp).Offset(1, 0)).PasteSpecial Paste:=xlPasteValues
            Set a = .FindNext(a)
            If a Is Nothing Then Exit Do
        Loop While Not a Is Nothing And a.Address <> firstAddress
    End If
    End With

Could you please help
Regards
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
I use the copy Pastespecial a little differently and it works. I don't think you can use it in conjuction with the copy function. It must be a seperate statement. Hope this helps.

Ex.

Activesheet.range("A1:Z90").copy
ActiveSheet.range("A91").pastespecial xlpastevalues
 

Swifey

Active Member
Joined
Jan 16, 2003
Messages
421
I'm struggling to get your code into my program.
Code:
    With Sheets("Wessex").Range("C1:C65536")
    Set a = .Find("Grand Total", LookIn:=xlValues)
    If Not a Is Nothing Then
        firstAddress = a.Address
        Do
            a.EntireRow.copy(Sheets("Sheet1").Activesheet.Range("A65536").End(xlUp).Offset(1, 0)).PasteSpecial Paste:=xlPasteValues
            Set a = .FindNext(a)
            If a Is Nothing Then Exit Do
        Loop While Not a Is Nothing And a.Address <> firstAddress
    End If
    End With
    Range("A1").Select

I'm at a loss in what to do next

Regards
 

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
Swifey said:
I'm struggling to get your code into my program.
Code:
    With Sheets("Wessex").Range("C1:C65536")
    Set a = .Find("Grand Total", LookIn:=xlValues)
    If Not a Is Nothing Then
        firstAddress = a.Address
        Do
            a.EntireRow.copy(Sheets("Sheet1").Activesheet.Range("A65536").End(xlUp).Offset(1, 0)).PasteSpecial Paste:=xlPasteValues
            Set a = .FindNext(a)
            If a Is Nothing Then Exit Do
        Loop While Not a Is Nothing And a.Address <> firstAddress
    End If
    End With
    Range("A1").Select

I'm at a loss in what to do next

Regards

Try changing
a.EntireRow.copy(Sheets("Sheet1").Activesheet.Range("A65536").End(xlUp).Offset(1, 0)).PasteSpecial Paste:=xlPasteValues

to

a.EntireRow.copy(Activesheet.Range("A65536").End(xlUp).Offset(1, 0))
a.range(Where you want to paste).pastespecial xlpastevalues

PS-I'm not sure what you are copying, the range("A65536").end(xlup).offset(1,0) will go to the last cell in column A and down 1. Does this row have infromation on it?? It would seem to me you aren't copying anything???
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,560
Messages
5,765,100
Members
425,258
Latest member
brentmitchell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top