Automate invoice

Here is a starting point.

This macro is layout dependent. I have assumed the layout of Sheet1 that you provided.

You need to name the ranges in the quotation as Item1, Item2, ... and the item number should correspond to the column in Sheet1. Modify the case statement (of the macro below) to include just the items you are interested in (currently set to 1 and 2).

To name a range, click in the cell and type the name in the 'cell reference area' which is to the left of the formula bar.

To install the macro, open your sheet, hit alt>f11. On the file menu go: insert>module and paste the code.

To run from the sheet go: tools>macro>macros, select update_invoice and click run.

Hope this helps.

Sub update_invoice()
Dim item(1000, 50) As String

start_row = 2
start_col = 1
row_count = Application.WorksheetFunction.CountA(Worksheets("Sheet1").Range("A:A")) - 1
col_count = Application.WorksheetFunction.CountA(Worksheets("Sheet1").Range("1:1"))

Worksheets("Sheet1").Select
Count = 0
Do While Count < row_count
  Count2 = 0
  Do While Count2 <= col_count
    item(Count, Count2) = Cells(Count + start_row, start_col + Count2).Value
    Count2 = Count2 + 1
  Loop
  Count = Count + 1
Loop

Worksheets("Sheet2").Select
Count = 0
Do While Count < row_count
  Count2 = 0
  Do While Count2 <= col_count
    Select Case Count2 + 1
    Case 1, 2
      Range("Item" & Count2 + 1).Value = item(Count, Count2)
    End Select
    Count2 = Count2 + 1
  Loop
  
  result_var = MsgBox("Would you like to print?", vbYesNo)
  If result_var = 6 Then
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
  End If
  
  Count = Count + 1
Loop

End Sub