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

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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
 
Upvote 0
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
 
Upvote 0
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???
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,072
Members
448,546
Latest member
KH Consulting

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
Back
Top