CODEWITHSUNDEEP
excelvbacopymatchpaste
VBAWARD
VBAWARD

Reputation: 71

Append Data to the Last Row of another Worksheet

I simply need to grab a column (range) from one sheet, and append to another sheet. For some reason I keep getting an error 1004 - object/application defined error when trying to run the paste values function.

Any help would be appreciated.

    Sub copycontactsiratotpsd()

    Dim LastRowIRA2 As Long
    Dim LastRowIRA As Long
    Dim LastRowPOV As Long
    Dim lastrow As Long




    'activate source sheet
    ActiveWorkbook.Worksheets("IRA").Activate

    'copy from rev to ira AG to match # of rows for TPRM Contacts before appending
    ActiveWorkbook.Sheets("Rev").Range("B2:B15000").SpecialCells(xlCellTypeVisible).Copy
    ActiveWorkbook.Sheets("IRA").Range("AG2:AG15000").PasteSpecial xlPasteValues

    'define last rows for all three instances
    LastRowIRA = ActiveSheet.Range("A1").CurrentRegion.Rows.count
    LastRowIRA2 = ActiveSheet.Range("AG1").CurrentRegion.Rows.count
    lastrow = WorksheetFunction.Max(Sheets("TPD").Cells(Rows.count, "A").End(xlUp).Row)

    LastRowPOV = ActiveWorkbook.Sheets("TPD").Range("A1").CurrentRegion.Rows.count

    'if the number of lastrow in source sheet is equal to total VISIBLE last row within reference sheet then
        If LastRowIRA = LastRowIRA2 Then

            ActiveWorkbook.Worksheets("IRA").Activate

            'copy the data needed, values are generally less than 10000 rows
            ActiveWorkbook.ActiveSheet.Range("B2:B10000").Copy

            ActiveWorkbook.Sheets("TPD").Range("A", lastrow).PasteSpecial xlPasteValues

'LINE WITH ERROR ABOVE




        'else display msg for error handling
        Else: MsgBox "Row Count is off! *CHECK*"

        End If


    ActiveWorkbook.Worksheets("IRA").Activate
    Columns(33).EntireColumn.Delete

    End Sub

Upvotes: 0

Views: 1630

Answers (1)

Cyril
Cyril

Reputation: 6829

To allow an answer to close this out: 

ActiveWorkbook.ActiveSheet.Range("B2:B10000").Copy
ActiveWorkbook.Sheets("TPD").Cells(lastrow, "A").PasteSpecial xlPasteValues

Or:

ActiveWorkbook.ActiveSheet.Range("B2:B10000").Copy
ActiveWorkbook.Sheets("TPD").Range("A" & lastrow).PasteSpecial xlPasteValues

Upvotes: 2

Related Questions