Forum Discussion

TARUNKANTI1964

Brass Contributor

May 09, 2022

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...

Macros and VBA

HansVogelaar

May 09, 2022

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

TARUNKANTI1964

Brass Contributor

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

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

HansVogelaar

MVP

May 09, 2022

TARUNKANTI1964

Is the row with INVOICE and Invoice # row 1?

TARUNKANTI1964

Brass Contributor

May 10, 2022

YES

HansVogelaar

MVP

May 10, 2022

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

TARUNKANTI1964
Brass Contributor
May 11, 2022
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.

Forum Discussion

M.S.EXCEL VBA

Share

Resources