Selected Answer
OK. Please try this code. Paste it to a standard code module.
Sub SaveButton_Click()
' 131
Dim Inv As Variant ' Invoice data
Dim Tmp As Variant ' Invoice items data
Dim Items As Variant ' Tmp w/o "Taxed" column
Dim R As Long ' row (diverse uses)
Dim C As Long ' loop counter: Tmp, 2
With Worksheets("Invoice 2")
Inv = .Range("F3:F6").Value
' each row must have a value in column A
R = .Cells(17, "A").End(xlDown).Row
Tmp = .Range(.Cells(17, "A"), .Cells(R, "F")).Value
End With
' remove data for column "Taxed" from the invoice items
ReDim Items(1 To UBound(Tmp), 1 To UBound(Tmp, 2) - 1)
For R = 1 To UBound(Tmp)
For C = 1 To UBound(Items, 2)
' removes the next to last item in a row
Items(R, C) = Tmp(R, C + IIf(C = UBound(Items, 2), 1, 0))
Next C
Next R
Application.ScreenUpdating = False
With Worksheets("DATA")
R = .Cells(.Rows.Count, "E").End(xlUp).Row + 1
.Range("A2:I2").Copy ' format new cells like first row
.Cells(R, "A").Resize(UBound(Items), UBound(Items, 2) + UBound(Inv)) _
.PasteSpecial xlPasteFormats
.Cells(R, "A").Resize(1, UBound(Inv)).Value = Application.Transpose(Inv)
.Cells(R, "E").Resize(UBound(Items), UBound(Items, 2)).Value = Items
End With
With Application
.CutCopyMode = False
.ScreenUpdating = True
End With
End Sub
Edit 04 Dec 2020 ===============================
If you wish to delete the data in the "Invoice" sheet after copying please replace the first code block in the above procedure with the one below.
With Worksheets("Invoice 2")
With .Range("F3:F6")
Inv = .Value
.ClearContents
End With
' each row must have a value in column A
R = .Cells(17, "A").End(xlDown).Row
With .Range(.Cells(17, "A"), .Cells(R, "F"))
Tmp = .Value
.ClearContents
End With
End With