May 09 2022 06:26 AM
Hi, Respected Support Teams, I want some TIPS regarding EXCEL VBA to solve my problem as given below. I have VBA Code got from this support teams and used it in my project as given below, but it not works properly. May I get help to correct the same.
I have an Excel work book Contains with three excel sheets named respectively 1) ITEM LIST , 2) INVOCE , 3) INVOICELIST.
I would like to enter data in cells range "D12:D36" as 'ITEM NAME' and in cells range "E12:E36" 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 (D12:D36) as 'ITEM NAME’, (E12:E36) as 'ITEM QUANTITY' and cell value of 'H44' as 'TOTAL INVOICE AMOUNT' from "INVOICE" Sheet would be transferred to Column cells "D2:BB2" 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”,"AZ2" 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”, "BA2" as 'ITEM QUANTITY’ and to cell no "BB2" 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 "D12:D36" and Cell range "E12:E36" 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:BA3", and “BB3”, and every time when I press the "SAVE Button" the event would be continuing to happen as above frequently.
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 |
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 |
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:BB on sheet INVOICELIST
t = wt.Range("D:BA").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 + 24).Value
wt.Cells(t, 2 * s + 3).Value = ws.Range("E" & s + 24).Value
Next s
wt.Range("BB" & t).Value = ws.Range("H44").Value
'Copy inv no
wt.Range("A" & t).Value = ws.Range("G1").Value
'Copy Date
wt.Range("B" & t).Value = ws.Range("G2").Value
'Copy Company Name
wt.Range("C" & t).Value = ws.Range("D3").Value
Application.ScreenUpdating = True
End Sub
May 09 2022 07:13 AM
SolutionI've made very little modifications to your code. Maybe this is what you are looking for.
May 09 2022 11:45 PM
May 10 2022 08:16 AM
You are welcome. I'm glad i was able to help you.
May 09 2022 07:13 AM
SolutionI've made very little modifications to your code. Maybe this is what you are looking for.