copy paste special

[Swifey]

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:

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

 

[Cbrine]

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]

I'm struggling to get your code into my program.

    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]

I'm struggling to get your code into my program.

    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???