M.S.EXCEL VBA

TARUNKANTI1964 

In your first post, the data on the INVOICE sheet were in rows 15 to 39. Now they are in rows 12 to 36.

The total on the INVOICE sheet is in H44 instead of H45, and on the INVOICELIST sheet in column BB instead of AZ.

The invoice number on the INVOICE sheet is in F1 instead of in G1, and on the INVOICELIST in column A instead of in column B.

The invoice date has moved too.

So the code must be adjusted.

Sub SaveData()
    Dim ws As Worksheet
    Dim wt As Worksheet
    Dim s As Long
    Dim t As Long
    Dim r As Long
    Application.ScreenUpdating = False
    Set ws = Worksheets("INVOICE")
    Set wt = Worksheets("INVOICELIST")
    ' Find first empty row in columns D:AL on sheet INVOICELIST
    t = wt.Range("D:AY").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
    ' Copy Item Name and Item Quantity
    For s = 1 To 25
        wt.Cells(t, 2 * s + 2).Value = ws.Range("D" & s + 11).Value
        wt.Cells(t, 2 * s + 3).Value = ws.Range("E" & s + 11).Value
    Next s
    wt.Range("BB" & t).Value = ws.Range("H44").Value
    'Copy inv no
    wt.Range("A" & t).Value = ws.Range("F1").Value
    'Copy Date
    wt.Range("B" & t).Value = ws.Range("G2").Value
    'Copy Company Name
    wt.Range("C" & t).Value = ws.Range("D3").Value
    ' Clear Item Name and Item Quantity
    ws.Range("D12:E36").ClearContents
    ' Optional: clear Invoice Number, Date and Company Name
    ws.Range("D3,F1,G2").ClearContents
    Application.ScreenUpdating = True
End Sub