Extracting Data from Rows using a Macro(s)

[A17BPG]

Below is sample of what my spreadsheet may look like:

Name	Tel.#	PoultryReq	Wght.	PorkReq.	Wght.	BeefReq.	Wght.	Bacon	Wght.
Fred	123	Turkey	10.00			Roasting	3.00	Back	1.50
George	456			Leg	4.00			Gammon	4.00
Arnold	789					Steak	2.00	Streaky	1.00

<tbody>
</tbody>

What I want to do is get data from rows to generate an invoice listing only the data held in rows and ignoring all the empty cells. The results being passed to new sheet called invoice and listed as below, my original sheet has 54 columns and possibly up to 1000 rows so would want to go through list one row at a time and the print one invoice at a time.

Name
Fred		Turkey	10.00	@1.20	12.00
		Roasting	3.00	@4.50	13.50
		Back	1.50	@2.50	3.70
		Invoice	Total		29.20

<tbody>
</tbody>

Thanks

 

[Veritan]

Hello, I'll need some additional information before I can get you a full solution. What the cell addresses where your information is stored? Specifically, in your original table, is "Name" in cell A1, "Tel.#" in cell B1, and so on? Does the same apply to the second table, just on a different tab? Also, where are the prices coming from? Doing what you are asking about is definitely possible, but I need to know more before I'll be able to help you completely.

 

[Veritan]

This is a partial solution I was able to come up with. I've made some assumptions on the location of the cells. However, without knowing where the unit prices are, this code just puts 0 in for the cost of each item. I've left some comments where you can edit this to put in some cost retrieval code if you'd like to try that yourself.

Sub GenerateInvoices()
    Dim wsList As Worksheet, wsTemplate As Worksheet, Customer As Range, OrderRange As Range, Order As Range, Cost As Double
    
    Set wsList = Sheets("Sheet1") [COLOR=#008000]'Change the name here to be the name of the actual sheet with the list of who bought from you.[/COLOR]
    Set wsTemplate = Sheets("Invoice Template") [COLOR=#008000]'This may be changed if you want the tab to be named something other than "Invoice Template".[/COLOR]
    
    For Each Customer In Range(wsList.Cells(2, "A"), wsList.Cells(Rows.Count, "A").End(xlUp))
        wsTemplate.Cells(2, "A") = Customer
        Set OrderRange = Range(wsList.Cells(Customer.Row, "C"), wsList.Cells(Customer.Row, Columns.Count).End(xlToLeft))
        
        For Each Order In OrderRange
            If Order <> "" And IsNumeric(Order) = False Then
[COLOR=#008000]                'Insert cost retrieval code here, this could be a function if the code to do this ends up being more complex than just a line or two.[/COLOR]
[COLOR=#008000]                'Cost = ...[/COLOR]
                With wsTemplate.Cells(Rows.Count, "C").End(xlUp).Offset(1)
                    .Value = Order
                    .Offset(, 1) = Order.Offset(, 1)
                    .Offset(, 2) = Cost
                    .Offset(, 3) = .Offset(, 1) * Cost
                End With
            End If
        Next Order
        
        With wsTemplate.Cells(Rows.Count, "C").End(xlUp).Offset(2)
            .Value = "Invoice"
            .Offset(, 1) = "Total"
            .Offset(, 3) = WorksheetFunction.Sum(Range(wsTemplate.Cells(2, "F"), wsTemplate.Cells(Rows.Count, "F").End(xlUp)))
        End With
        
        wsTemplate.PrintOut
        wsTemplate.UsedRange.Offset(1).ClearContents
    Next Customer
End Sub

 

[A17BPG]

Hi yes the Name, Tel.# etc. are along the top of sheet, as you ask A1,B1, C1 etc.There is "Price per Kilo" column after each "wght" column sorry I did not show these as short on space. Reference the second Tab "Invoice" I would like to present the data retrieved as you would / might find in a normal Invoice i.e. Customer # then Customers Name. Then below first item purchased then "wght" , "price per kilo" then a calculated field to give price of item. Then the following line the same for the next item purchased, finally at the end a calculated field to give a grand total of the invoice. As this system will be for a butchers at the moment VAT Tax is not involved so a Tax field would not be required, maybe later this could be added if needed. Many Thanks Brian

 

[Veritan]

I believe this code will work based on where the unit price is. If you want to add a Customer #, then I would also need to know where that is in the original sheet, as well as where you want it to go in the Invoice template. When you're working code, it's very important to be as detailed as possible about where information is and where you want it to go.

Sub GenerateInvoices()
    Dim wsList As Worksheet, wsTemplate As Worksheet, Customer As Range, OrderRange As Range, Order As Range
    
    Set wsList = Sheets("Sheet1")
    Set wsTemplate = Sheets("Invoice Template")
    
    For Each Customer In Range(wsList.Cells(2, "A"), wsList.Cells(Rows.Count, "A").End(xlUp))
        wsTemplate.Cells(2, "A") = Customer
        Set OrderRange = Range(wsList.Cells(Customer.Row, "C"), wsList.Cells(Customer.Row, Columns.Count).End(xlToLeft))
        
        For Each Order In OrderRange
            If Order <> "" And IsNumeric(Order) = False Then
                With wsTemplate.Cells(Rows.Count, "C").End(xlUp).Offset(1)
                    .Value = Order
                    .Offset(, 1) = Order.Offset(, 1)
                    .Offset(, 2) = Order.Offset(, 2)
                    .Offset(, 3) = .Offset(, 1) * .Offset(, 2)
                End With
            End If
        Next Order
        
        With wsTemplate.Cells(Rows.Count, "C").End(xlUp).Offset(2)
            .Value = "Invoice"
            .Offset(, 1) = "Total"
            .Offset(, 3) = WorksheetFunction.Sum(Range(wsTemplate.Cells(2, "F"), wsTemplate.Cells(Rows.Count, "F").End(xlUp)))
        End With
        
        With wsTemplate
            .Columns.AutoFit
            .PrintOut
            .UsedRange.Offset(1).ClearContents
        End With
    Next Customer
End Sub

 

[A17BPG]

Cust.#	Cust.Name	Tel.#	Poultry	Wght.	Cost	Beef	Wght	Cost
1	Fred	123	Turkey	10.00	11.00
2	George	456				T.side	3.00	6.00
3	Brian	789	Chicken	3.50	8.00	S.loin	2.50	12.00

<tbody>
</tbody>

Hi Veritan I am on a very nice relaxing holiday at the moment and I am using my iPad to communicate with you so will try the code this weekend when home. Reference the question of the customer Number this is the first field i.e. A1. The Invoice layout should hopefully look something like below with the Cust.# being on the left followed by Cust. Name then below line by line the purchases that have been made again calculated field for each item and the total calculated field at bottom of invoice.

			Invoice
3	Brian
	Item	Wght	Cost	Total
	Chicken	3.50	8.00	28.00
	S.loin	2.50	12.00	30.00
			G.Total	58.00

<tbody>
</tbody>

Thanks Brian

 

[Veritan]

Okay, I think this is what you are looking for. Note that on the Invoice Template tab, I have the headers Item, Wght, Cost, and Total in Row 4, starting in Column B (make sure to put them in and format them however you want before you run the code). The word "Invoice" is already in the template and formatted however you want it to appear. At the very top of the code, I declare a constant that holds the currency number format. I'm using USD, so you'll probably want to change the sign to be whatever currency you're using. See if this works for you.

Const localCurrency As String = "$#,##0.00"
Sub GenerateInvoices()
    Dim wsList As Worksheet, wsTemplate As Worksheet, Customer As Range, OrderRange As Range, Order As Range


    Set wsList = Sheets("Sheet1")
    Set wsTemplate = Sheets("Invoice Template")
    
    For Each Customer In Range(wsList.Cells(2, "B"), wsList.Cells(Rows.Count, "B").End(xlUp))
        wsTemplate.Cells(3, "A") = Customer.Offset(, -1)
        wsTemplate.Cells(3, "B") = Customer
        Set OrderRange = Range(wsList.Cells(Customer.Row, "D"), wsList.Cells(Customer.Row, Columns.Count).End(xlToLeft))
        
        For Each Order In OrderRange
            If Order <> "" And IsNumeric(Order) = False Then
                With wsTemplate.Cells(Rows.Count, "B").End(xlUp).Offset(1)
                    .Value = Order
                    .Offset(, 1) = Order.Offset(, 1)
                    .Offset(, 1).NumberFormat = "0.00"
                    .Offset(, 2) = Order.Offset(, 2)
                    .Offset(, 2).NumberFormat = localCurrency
                    .Offset(, 3) = .Offset(, 1) * .Offset(, 2)
                End With
            End If
        Next Order
        
        With wsTemplate.Cells(Rows.Count, "D").End(xlUp).Offset(2)
            .Offset(, 1) = WorksheetFunction.Sum(Range(wsTemplate.Cells(2, "E"), wsTemplate.Cells(Rows.Count, "E").End(xlUp)))
            Range(wsTemplate.Cells(5, "E"), wsTemplate.Cells(Rows.Count, "E").End(xlUp)).NumberFormat = localCurrency
            .Offset(, 1).Borders(xlEdgeBottom).LineStyle = xlDouble
            wsTemplate.Columns.AutoFit
            .Value = "Invoice Total"
            .HorizontalAlignment = xlRight
        End With
        
        With wsTemplate
            .PrintOut
            With Union(.UsedRange.Offset(4), .Cells(3, "A").Resize(1, 2))
                .ClearFormats
                .ClearContents
            End With
        End With
    Next Customer
End Sub

 

[A17BPG]

Hi very many thanks will try this out asap when home and let you know results. Thanks again.

 

[A17BPG]

Hi Veritan,
Tried Macro out when home but unfortunately got an error box also it did not produce the display I would like.
I have saved a file in excel showing results from macro also an Order Entry Sheet and an Invoice Layout I would like to try to achieve, but I do not know if or how to attach a file to this reply.
If it is not possible to attach a file to this reply perhaps I could email you it.

Many Thanks
Brian

 

[Mike Szczesny]

Please let me know if you would be interested in a non-macro solution for this.