VBA for extract data from invoice

0

 hi,

i want to extract daa from invoice such as INV NO / DATE / CUS ID/ ..

My inventory sheet coloumn no follows  as above, want to find the last empty row of the 

inventory sheet and input these datas, i found the last row but unable to input values in the desired  cells 

find the attached sample file and help me to sove this issue

Note : As i deleted other workshhets in the workbook kindly change the sheet no in the

macro 

Answer
Discuss

Answers

0
Selected Answer

Hi Ramzein (see revision note below)

In your first line of code, you're trying to find the next free row number in "Invoice Inventory" (based on column 9) but you select select the entire row (which isn't necessary) so you don't get ERow. 

In the attached revised workbook, you'll see I've set Erow as an integer then used this modification of your statement will get the row number you need for Erow (but based on column 1 contents)

ERow = Sheet11.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

Then values from the Invoice (sheet13) can be imported using statements like:

Sheet11.Cells(ERow, 1).Value = Sheet13.Cells(22, 8).Value

In the full code below, you'll see I've extracted some cells (but not all) within a With.. End With (to save repeating "Sheet11"). Note that for cells E#, F# and G# I've extracted the data from the data now imported in cell D#.

If you run the code, it will keep repeating data from the Invoice sheet in new rows (though in practice I guess you will only run the macro once you've created a new invoice) .

REVISION: in the revised file attached (following your comments) I've defined a new variable (ItemCount As Integer) and used that to extract item data ALONG WITH the common invoice data. This means you could filter on the Inventory to see all sales of a particular item say).

There's now a For/ Next loop where (if the ItemCode on the invoice isn't blank), the common data is copied along with the column data you indicated from that row number on the Invoice. In that loop, ERow is increased so a new row could be written next time.

If the ItemCode hasn't been completed, it skips to Finish where the user is told how many rows were copied. This means if there are only 3 items on the Invoice (I've added  imaginary data), the Esle statement on the fourth loop causes it to exit the sub so only 3 rows are copied to the Inventory Invoice sheet.

Please try.

Given your Invoice is probably a fixed template, you might use Name Manager (and Names in the code) to make the code more succinct and read better.

Hope this helps. If so, please Select this answer.

Sub AddMain()

Dim ERow As Integer, ItemCount As Integer

ERow = Sheet11.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row ' changed to column 1 not 9 i.e if there's an invoice number

With Sheet11 ' in Invoice Inventory sheet...

For ItemCount = 34 To 43

    If Sheet13.Cells(ItemCount, 4) <> "" Then ' used Item Code since No contains a formula
    'add the common data
        .Cells(ERow, 1).Value = Sheet13.Cells(22, 8).Value 'copy value from relevant cell on logical sheet 1 "Invoice"
        .Cells(ERow, 2).Value = Sheet13.Cells(20, 5).Value ' etc.
        .Cells(ERow, 3).Value = Sheet13.Cells(20, 8).Value
        .Cells(ERow, 4).Value = Sheet13.Cells(21, 8).Value
            .Cells(ERow, 5).Value = Day(.Cells(ERow, 4)) ' Get day from date in D
            .Cells(ERow, 6).Value = MonthName(Month(.Cells(ERow, 4))) ' get the month name from month number of date in D
            .Cells(ERow, 7).Value = Year(.Cells(ERow, 4)) ' get the year from date in D
        .Cells(ERow, 8).Value = Sheet13.Cells(21, 5).Value
     'add the item data
        .Cells(ERow, 9).Value = Sheet13.Cells(ItemCount, 3).Value
        .Cells(ERow, 10).Value = Sheet13.Cells(ItemCount, 5).Value
        .Cells(ERow, 11).Value = Sheet13.Cells(ItemCount, 7).Value
        .Cells(ERow, 12).Value = Sheet13.Cells(ItemCount, 9).Value
    
     ERow = ERow + 1 'increase sheet 11 row counter
    
    Else
        GoTo Finish
    
    End If
    
Next ItemCount

End With

Finish:

MsgBox "Copied invoice details with " & ItemCount - 34 & " item rows"

End Sub
Discuss

Discussion

hi John,
Thank you so much for your quick response
Coloumn 1 to 7 each cell ref taken from inv but 
as Coloumn 9,11 and 12  has multiple cells i shoul define as coloumn 9 (34:43, 4) 
means    .Cells(ERow, 9).Value = Sheet13.Cells(34:43, 4).Value. or
.Cells(ERow, 9).Value = Sheet13.Cells((34,4:43,4)).Value.

(as per my invoice cloumn 4,7 and 8 has multiple cells are to be copied to sheet11) 

im not sure with both kindly let me know the correct one 

Thanks
Ramzein (rep: 8) Nov 7, '20 at 2:17 pm
hi,

You have provided more than i asked for thank you so much again for revising the attached file .
more over i just want to clarify as im just a beginer for VBA why row no are given
instead of letter ? normally i thougth first row letter and thn cell no ?

i repeat THANK YOU SO MUCH for your concern



 
Ramzein (rep: 8) Nov 8, '20 at 5:15 am
Ramzein.

Thanks for selecting my answer.

I think Row returns a number (rather than a letter) because the earliest spreadsheets referred to cells only by row and column numbers. Not sure but Don, Variatus or others may know. 

On learning VBA (and Excel's true power) I'd recommend you subscribe to TeachExcel - it certainly helped me to learn what (little) I know. Check out the many tutorials from Don!
John_Ru (rep: 1002) Nov 8, '20 at 6:01 am
How to run macro if these sheets are protected? as i want to protect the invoice sheet as it has vlookup for CUS ID,ITEM CODE etc..
inventory sheet to protect the whole data as it connected to inventory

Kindly explain
Ramzein (rep: 8) Nov 18, '20 at 11:07 pm
Ramzein. Following the rules of the Forum, this should be a new question (and may be answered by others).

Please post a new question (with file) and refer to protected Worksheets or cells in the subject. 
John_Ru (rep: 1002) Nov 19, '20 at 1:33 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login