EXCEL VBA

Contributor

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

 

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

1 Reply

@TARUNKANTI1964 

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.