Forum Discussion
Solved
M.S.EXCEL VBA
Hi, I am completely beginner for EXCEL VBA, here I have created an Excel work book having Three worksheets, where I would like to enter data in cells range "D15:D39" as 'ITEM NAME' and in cells range "E15:E39" as 'ITEM QUANTITY' in the “INVOICE” Sheet along with "SAVE" Button. When I press “SAVE” button on “INVOICE” Sheet, and then that data range from (D15:D39) as 'ITEM NAME' , (E15:E39) as 'ITEM QUANTITY' and cell value of "H45" as 'TOTAL INVOICE AMOUNT' from "INVOICE SHEET" would be transferred to Column cells "D2:AY2" of "INVOICE LIST" sheet, to Cell No. "D2","F2","H2","J2","L2","N2","P2","R2","T2","V2","X2","Z2","AB2","AD2","AF2","AH2","AJ2",”AL2”,”AN2”,”AP2”,”AR2”,”AT2””AV2”,”AX2” as 'ITEM NAME'
and to Cell no
"E2","G2","I2","K2","M2","O2","Q2","S2","U2","W2","Y2","AA2","AC2","AE2","AG2","AI2","AK2",”AM2”,”AO2”,”AQ2”,”AS2”,”AU2”,”AW2”,”AY2”,as 'ITEM QUANTITY' and to cell no "AZ2" as 'TOTAL INVOICE AMOUNT' and contains of schedule cell of “INVOICE” Sheet would be clear except FORMULA. Then if I enter again data to Cell range "D15:D39" and Cell range "E15:E39" in my "INVOICE" Sheet for create new invoice and press the SAVE button on "INVOICE" Sheet I would need the contains of "INVOICE" Sheet would be transfer except FORMULA to "INVOICE LIST" Sheet to the next available blank row "D3:AY3", and “AZ3”, and every time when I press the "SAVE Button" the event would be continuing to happen as above frequently. but it not works. May I get help to correct the same I have Code which i wrote as below
Sub SaveData()
Dim ws As Worksheet
Dim wt As Worksheet
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
r = wt.Range("D:AY").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
wt.Range("D" & r).Resize(1, 25).Value = Application.Transpose(ws.Range("D15:D39"))
r = wt.Range("D:AY").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
wt.Range("E" & r).Resize(1, 25).Value = Application.Transpose(ws.Range("E15:E39"))
wt.Range("AZ" & r).Value = ws.Range("H45").Value
'Copy inv no
Sheets("INVOICELIST").Range("B" & r).Value = Sheets("INVOICE").Range("G1").Value
'Copy Date
Sheets("INVOICELIST").Range("A" & r).Value = Sheets("INVOICE").Range("G2").Value
'Copy Company Name
Sheets("INVOICELIST").Range("C" & r).Value = Sheets("INVOICE").Range("D3").Value
r = r + 1
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
7 Replies
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- Respected Mr. Hans Vogelaar
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
