SOLVED

M.S. EXCEL VBA

Brass Contributor

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!

 

   INVOICEInvoice #:1 
     Invoice Date09-05-2022 
 CUSTOMER: MAYNA    
        
        
        
        
        
        
        
 Sr.No.HSN CODEITEM NAMEQtyUnit PriceTotal Value 
 1 ABCD1011110 
 2 ABCD5511605 
 3      
 4      
 5 EFGH179153 
 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 DATECONSUMER NAMEItem NAMEQTYItem NAMEQTYItem NAMEQTYItem NAMEQTYItem NAMEQTYItem NAMEQTYItem NAMEQTYItem NAMEQTYItem NAMEQTYItem NAMEQTYItem NAMEQTYItem NAMEQTYItem NAMEQTYItem NAMEQTYItem NAMEQTYItem NAMEQTYItem NAMEQTYItem NAMEQTYItem NAMEQTYItem NAMEQTYItem NAMEQTYItem NAMEQTYItem NAMEQTYItem NAMEQTYItem NAMEQTYVALUE

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

 

 

3 Replies
best response confirmed by Grahmfs13 (Microsoft)
Solution

@TARUNKANTI1964 

I've made very little modifications to your code. Maybe this is what you are looking for.

 
 
Respected Quadruple_Pawn,
Yes the VBA Code what You have sent to me, that is work properly, what I am wanting. I have no language to express You " Many Many Thanks Very Much ".
Shall be Your obidient. again many thanks to You.

@TARUNKANTI1964 

You are welcome. I'm glad i was able to help you.

1 best response

Accepted Solutions
best response confirmed by Grahmfs13 (Microsoft)
Solution

@TARUNKANTI1964 

I've made very little modifications to your code. Maybe this is what you are looking for.

 
 

View solution in original post