M.S.EXCEL VBA

[TARUNKANTI1964]

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

[#2 · Accepted solution]

@TARUNKANTI1964 

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

[#3]

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.

[#4]

@TARUNKANTI1964 

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

[#5]

@TARUNKANTI1964 

Is the row with INVOICE and Invoice # row 1?

[#6]

YES

[#7]

@TARUNKANTI1964 

In your first post, the data on the INVOICE sheet were in rows 15 to 39. Now they are in rows 12 to 36.

The total on the INVOICE sheet is in H44 instead of H45, and on the INVOICELIST sheet in column BB instead of AZ.

The invoice number on the INVOICE sheet is in F1 instead of in G1, and on the INVOICELIST in column A instead of in column B.

The invoice date has moved too.

So the code must be adjusted.

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 + 11).Value
        wt.Cells(t, 2 * s + 3).Value = ws.Range("E" & s + 11).Value
    Next s
    wt.Range("BB" & t).Value = ws.Range("H44").Value
    'Copy inv no
    wt.Range("A" & t).Value = ws.Range("F1").Value
    'Copy Date
    wt.Range("B" & 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("D12:E36").ClearContents
    ' Optional: clear Invoice Number, Date and Company Name
    ws.Range("D3,F1,G2").ClearContents
    Application.ScreenUpdating = True
End Sub

[#8]

Respected Mr. Hans Vogelaar
Many Many Thanks to You for Your Valuable TIPS for me. Again Thank You Sir.
I shall be highly Obliged to You For help me.