I am new here and also new in Excel VBA. After viewing different YouTube videos and online platforms I have tried to make this .xlsm file in excel to transfer Invoice data in another sales sheet. But there is an error and I can not rectify it.
I also want to transfer Invoice Grand total amount to Sales sheet.
Please help me.
The VBA Code is Here
Dim i As Integer
Dim a As Integer
Dim inv As Range
Dim invds As Range
i = 2
Set invds = Sheets("SALES").Range("G:Q")
Do Until WorksheetFunction.CountA(invds.Rows(i)) = 0
i = i + 1
Loop
Set inv = Sheets("INVOICE").Range("B11:L26")
'With Sheets("Invoice")
'Dim lastRow As Long
'Dim Rng As Range
'lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
'Set Rng = .Range(.Cells(11, 2), .Cells(lastRow, 12))
'End With
For a = 1 To inv.Rows.Count
If WorksheetFunction.CountA(inv.Rows(a)) <> 0 Then
invds.Rows(i).Value = inv.Rows(a).Value
Sheets("SALES").Range("A" & i).Value = Sheets("INVOICE").Range("L6").Value
Sheets("SALES").Range("B" & i).Value = Sheets("INVOICE").Range("L5").Value
Sheets("SALES").Range("C" & i).Value = Sheets("INVOICE").Range("C5").Value
Sheets("SALES").Range("D" & i).Value = Sheets("INVOICE").Range("C7").Value
Sheets("SALES").Range("E" & i).Value = Sheets("INVOICE").Range("C8").Value
Sheets("SALES").Range("F" & i).Value = Sheets("INVOICE").Range("L7").Value
i = i + 1
End If
Next aAssumes that "Type" is the same as "SI No" and that C29 is the "Invoice Total".
Change as required.
Make sure that the unused cells in Column A between A10 and A34 are empty.
Copy and paste code into a regular module.
Sub Maybe()
Dim invArr, i As Long
For i = 11 To Sheets("Invoice").Cells(10, 1).End(xlDown).Row
With Sheets("Invoice")
invArr = Array(.Cells(6, 12), .Cells(5, 12), .Cells(5, 3), .Cells(7, 3), .Cells(8, 3), .Cells(i, 1), .Cells(i, 2), _
.Cells(i, 3), .Cells(i, 4), .Cells(i, 5), .Cells(i, 6), .Cells(i, 7), .Cells(i, 8), .Cells(i, 9), _
.Cells(i, 10), .Cells(i, 11), .Cells(i, 12), .Cells(29, 3))
End With
Sheets("Sales").Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, 18).Value = invArr
Next i
End SubDon’t have an account yet? Register yourself now and be a part of our community!