Macro to consolidate data and paste after last row

I'm trying to write a macro that will consolidate data from 2 sheets and paste them in a new sheet one after another. I'm having problem with the second part of my code.

I'm getting an

application or object defined error.

I think the range might be an issue - I want to copy all the used rows of column D from the Dump sheet and paste them after the last used row in column K in the Summary sheet. This is what I have so far

Sub Paste()
    Dim lRow3 As Long
    Dim rng3 As Range

    With ThisWorkbook
        With .Sheets("Dump Lease & RMP Charges")
            lRow3 = .Cells(.Rows.count, 1).End(xlUp).Row
            Set rng3 = .Range("D3:D" & lRow3)
            rng3.Copy Destination:=ThisWorkbook.Sheets("Summary Invoice ex").Range("K6")
        End With

        With Sheets("Dump MMS Service and Repairs")
            .Range(.Range("D3").End(xlToRight)).Copy 'line with error
        End With

        With Sheets("Summary Invoice ex")
            .Cells(.Rows.count, "K").End(xlUp).Offset(1, 0).PasteSpecial
        End With
    End With
End Sub

I changed the last part to

 With .Sheets("Dump MMS Service and Repairs")
    lRow4 = .Cells(.Rows.count, 1).End(xlUp).Row
    Set rng4 = .Range("D3:D" & lRow4)
    rng4.Copy Destination:=ThisWorkbook.Sheets("Summary Invoice ex").Cells(lRow4 + 1, "K")

 End With

Now there is no error but nothing is pasting!

3 answers

  • answered 2018-10-15 12:32 Will.D

    An Application or Object defined error. Generally means there is a typo somewhere.

    Your second with statement has a bullet point.

    With .Sheets("Dump Lease & RMP Charges")
    

    Should it not be

    With Sheets("Dump Lease & RMP Charges")
    

    Hopefully this gets rid of the Application/object error.

  • answered 2018-10-15 12:44 JosephC

    Got rid of nested With's, and made the change @Chronocidal suggested.

    Sub Paste()
        Dim lRow3 As Long
        Dim rng3 As Range
    
        With ThisWorkbook.Sheets("Dump Lease & RMP Charges")
                lRow3 = .Cells(.Rows.Count, 1).End(xlUp).Row
                Set rng3 = .Range("D3:D" & lRow3)
                rng3.Copy Destination:=ThisWorkbook.Sheets("Summary Invoice ex").Range("K6")
        End With
    
        ThisWorkbook.Sheets("Dump MMS Service and Repairs").Range("D3").End(xlToRight).Copy 'line with error
    
        With ThisWorkbook.Sheets("Summary Invoice ex")
            .Cells(.Rows.Count, "K").End(xlUp).Offset(1, 0).PasteSpecial
        End With
    
    End Sub
    

  • answered 2018-10-15 12:52 Pᴇʜ

    To copy all used rows in column D outgoing from cell D3 try

    .Range(.Cells(.Rows.Count, "D").End(xlUp), .Cells(3, .Columns.Count).End(xlToLeft)).Copy 
    

    instead of your error throwing line .Range(.Range("D3").End(xlToRight)).Copy in your original code.

    How it works:

    • .Cells(.Rows.Count, "D").End(xlUp) finds the last used row in column D
    • .Cells(3, .Columns.Count).End(xlToLeft) finds the last used column in row 3

    .Range makes a range of these two cells.


    Edit accordingly the comment:
    To copy rows in column D only use …

    .Range("D3", .Cells(.Rows.Count, "D").End(xlUp)).Copy