Invoicing

[Rob #4]

I am trying to figure out a way to sort this information by Description, so that I can compare Rates easily. However as time goes on I will need to add more invoices to this worksheet and will need those items added to the summary also. I am trying to do the summary on another worksheet. Any ideas?

National Waterworks Invoice Summary.xls
	A	B	C	D	E	F
23	Date	Invoice #	Description	Qty	Rate	Total
24	12/5/2005	3011461	16 TJ CL52 DI PIPE C/L	108	$33.48	$3,615.84
25	12/5/2005	3011461	16 EJIW GATE VLV RW OR	1	$3,355.00	$3,355.00
26	12/5/2005	3011461	5668S 2 CI CLEVE VLV BOX COMP	1	$75.00	$75.00
27	12/5/2005	3011461	16 MJ 45 BEND C/L CP DI C153	1	$355.00	$355.00
28	12/5/2005	3011461	16 MJ 22-1/2 BEND C/L CP DI C153	2	$354.50	$709.00
29	12/5/2005	3011461	16 MJ 11-1/4 BEND C/L CP DI C153	1	$356.00	$356.00
30	12/5/2005	3011461	16 MJ L/P SLEEVE CP DI C153	1	$287.50	$287.50
31	12/5/2005	3011461	16 MJ ACC SET L/ GLAND	12	$0.00	$0.00
32	12/5/2005	3011461	16 FIELD LOCK GASKET	3	$241.05	$723.15
33			Sales Tax			$710.74
34			Total			$10,187.23
35
36	12/8/2005	3035503	12 ONE-LOK DI RESTRAINT SLD12	8	$48.00	$384.00
37	12/8/2005	3035503	12 MJ ACC SET L/ GLAND	8	$0.00	$0.00
38	12/8/2005	3035503	12 MJ L/P SLEEVE CP DI C153	4	$113.19	$452.76
39			Sales Tax			$52.30
40			Total			$889.06
41
42	12/12/2005	3039947	12 ONE-LOK DI RESTRAINT SLD12	1	$48.00	$48.00
43	12/12/2005	3039947	12 MJ ACC SET L/ GLAND	2	$0.00	$0.00
44	12/12/2005	3039947	12 MJ CAP T/C CP DI C153	1	$59.78	$59.78
45	12/12/2005	3039947	12 MJ PLUG T/C CP DI C153	1	$60.76	$60.76
46			Sales Tax			$12.64
47			Total			$181.18
IX Invoice Data

 

[Smitty]

OK,

Here's some code for you. It will compile your invoices into a summary sheet, sort & subtotal:

<font face=tahoma><SPAN style="color:#00007F">Sub</SPAN> CreateSummary()
    <SPAN style="color:#00007F">Dim</SPAN> LR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    
        <SPAN style="color:#007F00">'   Create a Summary sheet from the activesheet</SPAN>
        ActiveSheet.Copy After:=Sheets(Sheets.Count)
        <SPAN style="color:#007F00">'   Rename the new sheet</SPAN>
        ActiveSheet.Name = "Summary"
        
        LR = Range("C65536").End(xlUp).Row
        <SPAN style="color:#007F00">'   Delete blank rows (Tax + Total)</SPAN>
        <SPAN style="color:#00007F">For</SPAN> i = LR <SPAN style="color:#00007F">To</SPAN> 1 <SPAN style="color:#00007F">Step</SPAN> -1
            <SPAN style="color:#00007F">If</SPAN> Cells(i, 1) = "" <SPAN style="color:#00007F">Then</SPAN> Cells(i, 1).EntireRow.Delete
        <SPAN style="color:#00007F">Next</SPAN> i
        
        <SPAN style="color:#007F00">'   Sort Summary & Subtotal</SPAN>
        <SPAN style="color:#00007F">With</SPAN> Range("C2")
            .Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlGuess, _
                OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
            .Subtotal GroupBy:=3, Function:=xlAverage, TotalList:=Array(4, 5, _
                6), Replace:=True, PageBreaks:=False, SummaryBelowData:=<SPAN style="color:#00007F">True</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
        
     Columns("C:C").EntireColumn.AutoFit
    
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Smitty