Cycle through table find last reference to order number then insert row

[Jack_881]

Hi Everyone

I have an excel table with line items from an order (each row represents a reference to an SKU). The purpose of this spreadsheet is to create an import document for XERO. Now XERO does not have an import field for Freight so the only way to do is to:

1. Cycle through the table looking for the last refernce to an order number
2. Insert a row below the last reference and insert a row
3. Copy data into that newly created row

An example

In the screen shot attached, the code would cycle through the table to find the last instance of InvoiceNumber N1082272, then insert a row below, make a copy of the data from the last instance row of the order number and change the InventoryItemCode to "Freight" and the Description to "Freight Cost". It would then continue down the table finding the last instance of order N1082328, rinse and repeat as per above.

Running this with VBA will save a lot of time as we currently have to insert the row, copy the data, change the SKU and Description manually.

Any help would be greatly appreciated.

 

[CSmith]

If you are inserting rows start from the last line and walk up and once the SKU number changes do the insert with fresh data

 

[Georgiboy]

Far from perfect but something like:

Sub test()
    Dim tbl As ListObject, tmp As String, x As Long
    Dim rng As Range
    
    Set tbl = Sheet1.ListObjects("Table1")
    Set rng = tbl.ListColumns("InvoiceNumber").DataBodyRange
    
    For x = rng.Row + rng.Rows.Count - 1 To rng.Row Step -1
        With Sheet1
            tmp = .Cells(x + 1, 1)
            If .Cells(x, 1) <> tmp Then
                .Cells(x + 1, 1).EntireRow.Insert
                .Cells(x, 1).EntireRow.Copy .Cells(x + 1, 1).EntireRow
                .Cells(x + 1, 1).Offset(, 8).Resize(, 2) = Array("Freight", "Freight Cost")
            End If
        End With
    Next x
    tbl.Resize tbl.Range.Resize(rng.Rows.Count + 2)
End Sub

 

[Domenic]

Or, assuming that the sheet containing the data is the active sheet, maybe...

Option Explicit

Sub test()

    Dim targetTable As ListObject
    Set targetTable = ActiveSheet.ListObjects("Table1") 'change the name of the table accordingly
   
    Dim currentListRow As ListRow
    Dim newListRow As ListRow
    Dim rowIndex As Long
    With targetTable.ListRows
        For rowIndex = .Count To 1 Step -1
            Set currentListRow = .Item(rowIndex)
            If currentListRow.Range.Cells(1).Value <> currentListRow.Range.Cells(2, 1).Value Then
                Set newListRow = .Add(rowIndex + 1)
                currentListRow.Range.Copy newListRow.Range
                With newListRow.Range
                    .Cells(1, 10).Value = "Freight"
                    .Cells(1, 11).Value = "Freight Cost"
                End With
            End If
        Next rowIndex
    End With
   
End Sub

Hope this helps!

 

[Jack_881]

Awesome. Have implemented the solution and hopefully others can use to import invoices into Xero and work around the fact you have to enter freight costs as a line item.