Forum Discussion
M.S.EXCEL VBA
Not bad for a beginner, you were almost there!
Try this:
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 + 14).Value wt.Cells(t, 2 * s + 3).Value = ws.Range("E" & s + 14).Value Next s wt.Range("AZ" & t).Value = ws.Range("H45").Value 'Copy inv no wt.Range("B" & t).Value = ws.Range("G1").Value 'Copy Date wt.Range("A" & 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("D15:E39").ClearContents ' Optional: clear Invoice Number, Date and Company Name ws.Range("D3,G1:G2").ClearContents Application.ScreenUpdating = True End Sub
Not bad for a beginner, you were almost there!
Try this:
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 + 14).Value
wt.Cells(t, 2 * s + 3).Value = ws.Range("E" & s + 14).Value
Next s
wt.Range("AZ" & t).Value = ws.Range("H45").Value
'Copy inv no
wt.Range("B" & t).Value = ws.Range("G1").Value
'Copy Date
wt.Range("A" & 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("D15:E39").ClearContents
' Optional: clear Invoice Number, Date and Company Name
ws.Range("D3,G1:G2").ClearContents
Application.ScreenUpdating = True
End Sub
Excellent! i have no language , what to say thank You. I am trying to use Your Tips, and I think it will work for me. Again I thank You.
shall be Your most obidient.
Respected Mr. Hans Vogelaar
a while ago i got Your tips and i used them in my excel project, but that is not working properly. That's why i reposted that matter for getting help from You. if there is any mistake by me please forgive me.
VBA CODE:
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 + 14).Value
wt.Cells(t, 2 * s + 3).Value = ws.Range("E" & s + 14).Value
Next s
wt.Range("AZ" & t).Value = ws.Range("H45").Value
'Copy inv no
wt.Range("B" & t).Value = ws.Range("G1").Value
'Copy Date
wt.Range("A" & 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("D15:E39").ClearContents
' Optional: clear Invoice Number, Date and Company Name
ws.Range("D3,G1:G2").ClearContents
Application.ScreenUpdating = True
End Sub
THIS IS INVOICE Sheet
INVOICE Invoice #: 1 Invoice Date 09-05-2022 CUSTOMER: MAYNA Sr.No. HSN CODE ITEM NAME Qty Unit Price Total Value 1 ABCD 10 11 110 2 ABCD 55 11 605 3 4 5 EFGH 17 9 153 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 Sub Total Tax Discount Total 868 This is INVOICELIST Sheet
INVOICE NO DATE CONSUMER NAME Item NAME QTY Item NAME QTY Item NAME QTY Item NAME QTY Item NAME QTY Item NAME QTY Item NAME QTY Item NAME QTY Item NAME QTY Item NAME QTY Item NAME QTY Item NAME QTY Item NAME QTY Item NAME QTY Item NAME QTY Item NAME QTY Item NAME QTY Item NAME QTY Item NAME QTY Item NAME QTY Item NAME QTY Item NAME QTY Item NAME QTY Item NAME QTY Item NAME QTY VALUE Is the row with INVOICE and Invoice # row 1?