Hi, I am completely beginner for EXCEL VBA, here I have created a Excel work book having Three worksheets, where I would like to enter data in cells range "C9: C25" as 'ITEM NAME' and in cells range "E9:E25" 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 (C9:C25) as 'ITEM NAME' , (E9:E25) as 'ITEM QUANTITY' and cell value of 'G33' as 'TOTAL INVOICE AMOUNT' from "INVOICE SHEET"would be transfered to Column cells "D2:AL2" of "INVOICE LIST"sheet, to Cell no "D2","F2","H2","J2","L2","N2","P2","R2","T2","V2","X2", "Z2","AB2","AD2","AF2","AH2","AJ2" as 'ITEM NAME' and to Cell no "E2","G2","I2","K2","M2","O2","Q2","S2","U2","W2","Y2", "AA2","AC2","AE2","AG2","AI2","AK2" as 'ITEM QUANTITY' and to cell no "AL2" 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 "C9:C25" and Cell range "E9:E25" 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:AK3", and “AL3”, and every time when I press the "SAVE Button" the event would be continue 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:AK").Find(What:="", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
wt.Range("D" & r).Resize(1, 17).Value = Application.Transpose(ws.Range("C9:C25"))
r = wt.Range("D:AK").Find(What:="", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
wt.Range("E" & r).Resize(1, 17).Value = Application.Transpose(ws.Range("E9:E25"))
wt.Range("AL" & r).Value = ws.Range("G33").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