May 09 2022 05:43 AM
Respected Mr. Hans Vogelaar
a while ago i got Your tips today and i used them in my excel project, but that is not working properly. That's why i am requesting in that matter for getting help from You. if there is any mistake by me please forgive me.
VBA CODE:
Sub SaveData()
Dim rng As Range
Dim temp As Variant
Dim i As Long
Dim a As Long
Dim rng_dest As Range
Application.ScreenUpdating = False
i = 1
Set rng_dest = Sheets("INVOICELIST").RangeColumn("D:T")
' Find first empty row in columns D:T on sheet INVOICELIST
Do Until WorksheetFunction.CountA(rng_dest.Rows(i)) = 0
i = i + 1
Loop
'Copy range C13:C29 and G35 on sheet Invoice to Variant array
Set rng = Sheets("INVOICE").Range("C13:C29")
Set rng = Sheets("INVOICE").Range("G35")
' Copy rows containing values to sheet INVOICELIST Column (D2:T2) for ITEM
' Copy rows containing values to sheet INVOICELIST Column (U2) for VALUE
For a = 1 To rng.Rows.Count
If WorksheetFunction.CountA(rng.Rows(a)) <> 0 Then
rng_dest.Rows(i).Value = rng.Rows(a).Value
'Copy inv no
Sheets("INVOICELIST").Range("B" & i).Value = Sheets("INVOICE").Range("F1").Value
'Copy Date
Sheets("INVOICELIST").Range("A" & i).Value = Sheets("INVOICE").Range("F2").Value
'Copy Company Name
Sheets("INVOICELIST").Range("C" & i).Value = Sheets("INVOICE").Range("D5").Value
i = i + 1
End If
Next a
Application.ScreenUpdating = True
End Sub
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:U on sheet INVOICELIST
r = wt.Range("D:U").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
wt.Range("D" & r).Resize(1, 17).Value = Application.Transpose(ws.Range("C13:C29"))
wt.Range("U" & r).Value = ws.Range("G35").Value
Application.ScreenUpdating = True
End Sub
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 |
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 |
May 09 2022 09:06 AM - edited May 09 2022 09:07 AM
You have posted this question in three different discussions, and you have received a reply in the two others.
This is very confusing, both for you and for the people who try to help you.
In the future, please don't start new discussions for the same question.