Copy and paste data in to next blank row of a table

[jasonmcasey]

Hi

I have the following code to copy and paste data to next empty row on another sheet, but it pastes it below the table I want to insert it in. I need to copy data from the ExceptImport table in to the ExceptionsTable table. E.g. ExceptImport[JobNumber] needs to go to ExceptionsTable[JOB NO] on the Exceptions sheet.

I believe I have to use ListObject as the destination in some manner but I am not sure how to do it. Please could someone have a look and let me know how I can adapt this to what i need:

Sub ExceptionUpdate()
With Sheets("Exceptions Import")
    .Range("ExceptImport[JobNumber]").Copy Destination:=Sheets("Exceptions").Range("A" & Rows.Count).End(xlUp).Offset(1)
    .Range("ExceptImport[Time]").Copy Destination:=Sheets("Exceptions").Range("G" & Rows.Count).End(xlUp).Offset(1)
    .Range("ExceptImport[Comment]").Copy Destination:=Sheets("Exceptions").Range("H" & Rows.Count).End(xlUp).Offset(1)
    .Range("ExceptImport[Short Code]").Copy Destination:=Sheets("Exceptions").Range("I" & Rows.Count).End(xlUp).Offset(1)
End With
End Sub

Regards
Jason

[patel45]

attach please a sample file

[millz]

Not sure if this is overkill, but you can try this:

Sub ExceptionUpdate()
    Dim r As Long
    With Sheets("Exceptions").ListObjects("ExceptionsTable").DataBodyRange
        r = .rows.Count
        If Application.CountA(.rows(r)) > 0 Then
            .ListObject.ListRows.Add
            r = r + 1
        Else
            Do While Application.CountA(.rows(r - 1)) = 0 And r > 1
                r = r - 1
            Loop
        End If
        
        .Cells(r, "A").Value = Sheets("Exceptions Import").Range("ExceptImport[JobNumber]").Value
        .Cells(r, "G").Value = Sheets("Exceptions Import").Range("ExceptImport[Time]").Value
        .Cells(r, "H").Value = Sheets("Exceptions Import").Range("ExceptImport[Comment]").Value
        .Cells(r, "I").Value = Sheets("Exceptions Import").Range("ExceptImport[Short Code]").Value
    End With
End Sub

[jasonmcasey]

Thanks Millz, that code does not work if there is no data in the table already and it only copies the first row from the ExceptImport table. I have attached a sample file which well help understand what I am trying to achieve.

On the Exceptions Import sheet, I have an XML table that imports data from an XML file. I then need to copy the data as follows:

From Sheet - Exceptions Import, Table ExceptImport, JobNumber column to Sheet - Exceptions, Table ExceptionsTable, JOB NO column
From Sheet - Exceptions Import, Table ExceptImport, Time column to Sheet - Exceptions, Table ExceptionsTable, Time column
From Sheet - Exceptions Import, Table ExceptImport, Comment column to Sheet - Exceptions, Table ExceptionsTable, Comments column
From Sheet - Exceptions Import, Table ExceptImport, ShortCode column to Sheet - Exceptions, Table ExceptionsTable, Error Code column

This is part of a larger file that is created once a week, I need to be able to copy that data to ExceptionsTable if it already has data in it or if it's blank.

[millz]

Hi jasonmcasey, try this amended code:

Sub ExceptionUpdate()
    Dim r As Long
    With Sheets("Exceptions")
        With .ListObjects("ExceptionsTable")
            If .ListRows.Count = 0 Then
                r = .HeaderRowRange.row + 1
            Else
                r = .DataBodyRange.rows.Count
                If Application.CountA(.DataBodyRange.rows(r)) > 0 Then
                    .ListRows.Add
                    r = r + 1
                Else
                    Do While Application.CountA(.DataBodyRange.rows(r - 1)) = 0 And r > 1
                        r = r - 1
                    Loop
                End If
                r = r + .HeaderRowRange.row
            End If
        End With
        Sheets("Exceptions Import").Range("ExceptImport[JobNumber]").Copy .Cells(r, "A")
        Sheets("Exceptions Import").Range("ExceptImport[Time]").Copy .Cells(r, "G")
        Sheets("Exceptions Import").Range("ExceptImport[Comment]").Copy .Cells(r, "H")
        Sheets("Exceptions Import").Range("ExceptImport[ShortCode]").Copy .Cells(r, "I")
    End With
End Sub

[jasonmcasey]

Hi Millz

I did not get chance to work on this again until today but that works perfectly. Thank you very much for your help