SAVING DATA OF LINE ITEMS

Occasional Visitor

How do I save my data from one sheet to another using vba? 

I want to save the data from one sheet to another and save by line item if possible. I have tried but don't seem to find the solution online. 

screenshot of result as attached 

 

Sheet 2 : result of saving the data in another sheetsSheet 2 : result of saving the data in another sheetsSheet 1: raw data from invoiceSheet 1: raw data from invoice

1 Reply


Sub saving()
Dim ar As Variant
Dim br()
With Sheets("invoice")
rem please unmerge any cells
rem all data should in a1:e25 without merge cell
ar = .Range("a1:e25")
End With
ReDim br(1 To UBound(ar), 1 To
For i = 8 To UBound(ar)
If Trim(ar(i, 1)) <> "" Then
n = n + 1
br(n, 1) = ar(2,2)
br(n, 2) = ar(2,3)
br(n, 3) = ar(2,5)
For j = 4 To 7
br(n, j ) = ar(i, j-2)
Next j
br(n, = ar(25,5)
End If
Next i
If n = "" Then MsgBox "no data!": Exit Sub
With Sheets("results")
r = .Cells(Rows.Count, 1).End(xlUp).Row + 1
.Cells(r, 1).Resize(n, UBound(br, 2)) = br
End With
End Sub