Automate printing with subtotals-plan trouble shooting.

[dandungan]

Hi MrExcel,

I'm asking for an alternate plan, because I'm unable to implement the plan below.

The plating department uses an old prodigy database to print hard copies of today's jobs. The database is so old we are unable to find a print driver that works on our network.

The plating manager asked me to emulate this data capture/print function.

Here's my plan that I'm unable to implement.

In Excel 2003 on Windows XP pro:

1. Collect data with userforms designed to appear and function as the prodigy database:
1. Store the data in sheet invoice data.
2. Filter data by date
A. By Date for daily report
B. By Month for Monthly Report
3. Clear Sheet DataForPrint to prepare for new data
3. Copy filtered data to DataForPrint
4. Apply Subtotals on Plating Code.
5. Print

My plan fails now because the autofilter part returns no data.

I have posted this, but I'm unable to get the autofilter to work.

I'm seeking suggestion for alternate approaches to print daily data with subtotals.

Thanks.

Dan

Data Sample:

Plating Code	Job Number	Job Date	ShipQuantity	Unit Price	Additional Charges	TotalAmount	Month	Year
55	694751-002	3/30/2013	4	0.8	0	3.2	3
55	695346-002	3/30/2013	190	0.6	0	114	3
55	693856-003	3/30/2013	9	0.6	0	5.4	3
55	694676-003	3/30/2013	4	0.6	0	2.4	3
55	691659-003	3/30/2013	4	0.4	0	1.6	3
55	689949-002	3/30/2013	26	0.6	0	15.6	3
55	694661-002	3/30/2013	21	0.6	0	12.6	3
55	689443-002	3/30/2013	32	0.6	0	19.2	3
55	694741-001	3/30/2013	2	0.4	0	0.8	3
55	694579-001	3/30/2013	15	0.6	0	9	3
55	693599-002	3/30/2013	6	0.6	0	3.6	3
55	694695-002	3/30/2013	15	0.6	0	9	3
55	694830-001	3/30/2013	25	0.4	0	10	3
55	694850-001	3/30/2013	50	0.6	0	30	3
55	690334-007	3/30/2013	176	0.6	0	105.6	3
55	692592-003	3/30/2013	4	0.6	0	2.4	3
55	693628-002	3/30/2013	4	1.2	0	4.8	3
55	695246-001	3/30/2013	2	0.8	0	1.6	3
55	695422-002	3/30/2013	1	0.8	0	0.8	3
55	694826-002	3/30/2013	1	0.8	0	0.8	3
55	695417-002	3/30/2013	13	0.6	0	7.8	3
55	693716-001	3/30/2013	112	0.6	0	67.2	3
55	691019-010	3/30/2013	2	0.9	0	1.8	3
55	691018-004	3/30/2013	2	0.9	0	1.8	3
55	693881-002	3/30/2013	2	0.8	0	1.6	3
55	694678-001	3/30/2013	540	0.4	0	216	3
55	695461-001	3/30/2013	55	0.6	0	33	3
55	693435-001	3/30/2013	50	0.6	0	30	3
55	694656-001	3/30/2013	50	0.6	0	30	3
55	692998-002	3/30/2013	15	0.6	0	9	3
55	693916-003	3/30/2013	20	0.9	0	18	3
55	694849-002	3/30/2013	39	0.6	0	23.4	3
55	694702-001	3/30/2013	68	0.6	0	40.8	3
55	694710-002	3/30/2013	53	0.9	0	47.7	3
55	692452-003	3/30/2013	38	0.6	0	22.8	3
55	693436-001	3/30/2013	10	0.9	0	9	3
55	693607-002	3/30/2013	102	0.6	0	61.2	3
55	694848-002	3/30/2013	6	0.6	0	3.6	3
56	693859-001	3/30/2013	250	0.45	0	112.5	3
56	694099-001	3/30/2013	295	0.45	0	132.75	3
56	695222-002	3/30/2013	20	0.45	0	9	3
56	693861-001	3/30/2013	15	0.3	0	4.5	3
55	694709-001	3/30/2013	25	0.6	0	15	3
56	684710-002	3/30/2013	32	0.45	0	14.4	3
56	693849-001	3/30/2013	50	0.6	0	30	3
51	695222-004	3/30/2013	20	0.3	0	6	3
3	693805-005	3/30/2013	4	0.6	0	2.4	3
3	694962-001	3/29/2013	1	0.4	0	0.4	3
3	687402-001	3/29/2013	143	0.3	0	42.9	3
21	687402-001	3/29/2013	143	0.3	0	42.9	3
3	694962-001	3/29/2013	1	0.4	0	0.4	3
3	694820-001	3/29/2013	4	0.4	0	1.6	3
3	693589-005	3/29/2013	2	0.4	0	0.8	3
3	694707-001	3/29/2013	85	0.2	0	17	3
3	693805-006	3/29/2013	4	0.4	0	1.6	3
3	693858-002	3/29/2013	3	0.2	0	0.6	3
1	675183-001	3/29/2013	50	0.4	0	20	3
1	693934-004	3/29/2013	90	0.6	0	54	3
1	693936-001	3/29/2013	535	0.4	0	214	3
3	692454-004	3/29/2013	11	0.2	0	2.2	3
51	693509-002	3/29/2013	10	0.3	0	3	3
56	692970-002	3/29/2013	5	0.6	0	3	3
56	693460-001	3/29/2013	15	0.45	0	6.75	3
56	693061-005	3/29/2013	50	0.3	0	15	3
56	693949-002	3/29/2013	18	0.45	0	8.1	3
56	693509-001	3/29/2013	10	0.4	0	4	3
56	690181-001	3/29/2013	10	0.6	0	6	3
56	694652-004	3/29/2013	41	0.3	0	12.3	3
56	692701-002	3/29/2013	5	0.45	0	2.25	3
48	695187-004	3/29/2013	2	0.6	0	1.2	3
48	695186-005	3/29/2013	1	0.45	0	0.45	3
48	695186-004	3/29/2013	1	0.45	0	0.45	3
48	695186-006	3/29/2013	1	0.45	0	0.45	3
48	695187-002	3/29/2013	2	0.6	0	1.2	3
48	695186-003	3/29/2013	1	0.45	0	0.45	3
48	695186-002	3/29/2013	1	0.45	0	0.45	3
56	690932-001	3/29/2013	48	0.45	0	21.6	3
56	693061-001	3/29/2013	25	0.3	0	7.5	3
56	693061-004	3/29/2013	25	0.45	0	11.25	3
56	693460-002	3/29/2013	20	0.45	0	9	3
56	693821-002	3/29/2013	15	0.45	0	6.75	3
56	695339-005	3/29/2013	4	0.45	0	1.8	3
56	695339-002	3/29/2013	2	0.45	0	0.9	3
56	695339-001	3/29/2013	2	0.45	0	0.9	3
56	695339-006	3/29/2013	2	0.45	0	0.9	3
56	695339-004	3/29/2013	2	0.3	0	0.6	3
51	695339-003	3/29/2013	2	0.3	0	0.6	3
56	692969-001	3/29/2013	5	0.4	0	2	3
56	690701-001	3/29/2013	5	0.45	0	2.25	3
56	693457-002	3/29/2013	1	0.3	0	0.3	3
57	692238-002	3/29/2013	76	0.45	0	34.2	3
56	694718-002	3/29/2013	35	0.6	0	21	3
56	693120-002	3/29/2013	10	0.45	0	4.5	3
56	694652-002	3/29/2013	41	0.45	0	18.45	3
56	691484-001	3/29/2013	5	0.45	0	2.25	3
56	692655-001	3/29/2013	5	0.45	0	2.25	3
56	692970-001	3/29/2013	5	0.3	0	1.5	3
56	693770-001	3/29/2013	15	0.45	0	6.75	3
56	693061-006	3/29/2013	25	0.3	0	7.5	3
56	675881-001	3/29/2013	25	0.45	0	11.25	3
56	690181-002	3/29/2013	10	0.45	0	4.5	3
56	690519-002	3/29/2013	30	0.45	0	13.5	3
56	692932-002	3/29/2013	106	0.45	0	47.7	3
56	693061-003	3/29/2013	25	0.3	0	7.5	3
48	695187-007	3/29/2013	2	0.6	0	1.2	3
48	695187-006	3/29/2013	1	0.6	0	0.6	3
56	693380-001	3/29/2013	506	0.45	0	227.7	3
56	695335-004	3/29/2013	6	0.6	0	3.6	3
56	686113-002	3/29/2013	37	0.45	0	16.65	3
55	695369-003	3/29/2013	406	0.9	0	365.4	3
55	694677-004	3/29/2013	106	0.9	0	95.4	3
55	693453-001	3/29/2013	6	0.8	0	4.8	3
55	695229-001	3/29/2013	35	0.6	0	21	3
55	694650-001	3/29/2013	25	0.6	0	15	3
55	692081-002	3/29/2013	14	0.9	0	12.6	3
55	693881-002	3/29/2013	2	0.6	0	1.2	3
55	695368-002	3/29/2013	753	0.6	0	451.8	3
55	694425-002	3/29/2013	11	1.6	0	17.6	3
55	688840-004	3/29/2013	26	0.9	0	23.4	3
55	689838-001	3/29/2013	19	0.6	0	11.4	3
55	694713-004	3/29/2013	9	0.9	0	8.1	3
55	694767-001	3/29/2013	35	0.4	0	14	3
55	694780-003	3/29/2013	3	1.2	0	3.6	3
55	694698-002	3/29/2013	31	0.6	0	18.6	3
55	695270-002	3/29/2013	868	0.9	0	781.2	3
55	694582-003	3/29/2013	5	0.6	0	3	3
55	693302-001	3/29/2013	100	0.6	0	60	3
55	694713-006	3/29/2013	25	0.6	0	15	3
55	694580-004	3/29/2013	1	0.8	0	0.8	3
55	693508-007	3/29/2013	2	0.9	0	1.8	3
55	695246-002	3/29/2013	4	0.8	0	3.2	3
55	692526-005	3/29/2013	1	0.9	0	0.9	3
55	694425-002	3/29/2013	116	0.6	0	69.6	3
55	695437-002	3/29/2013	9	0.9	0	8.1	3
55	695247-002	3/29/2013	20	0.6	0	12	3
55	694775-001	3/29/2013	44	0.6	0	26.4	3
55	694425-002	3/29/2013	4	1.2	0	4.8	3
55.9	692020-005	3/29/2013	1	0.6	0	0.6	3
55	695366-002	3/29/2013	113	0.6	0	67.8	3
55	694703-002	3/29/2013	200	0.6	0	120	3
55	692852-001	3/29/2013	10	0.4	0	4	3
55	693716-002	3/29/2013	224	0.4	0	89.6	3
55	694267-001	3/29/2013	45	0.6	0	27	3
55	694701-001	3/29/2013	68	0.6	0	40.8	3
55	693671-001	3/29/2013	100	0.4	0	40	3
55	692266-001	3/29/2013	40	0.4	0	16	3
55	693005-004	3/29/2013	14	0.6	0	8.4	3
55	694839-001	3/29/2013	15	0.6	0	9	3
55	694361-001	3/29/2013	20	0.6	0	12	3
55	695424-002	3/29/2013	7	0.6	0	4.2	3
55	693716-004	3/29/2013	112	0.6	0	67.2	3
55	693204-002	3/29/2013	10	0.9	0	9	3
55	692926-007	3/29/2013	201	0.6	0	120.6	3
55	692449-003	3/29/2013	26	0.4	0	10.4	3
55	692452-005	3/29/2013	130	0.4	0	52	3
55	691365-001	3/29/2013	484	0.4	0	193.6	3
55	694563-005	3/29/2013	1010	0.4	0	404	3
55	694677-004	3/29/2013	87	0.9	0	78.3	3
55.9	695225-000	3/29/2013	54	1.6	0	86.4	3
55	691872-001	3/29/2013	60	0.6	0	36	3
55	694924-001	3/29/2013	10	0.8	0	8	3
55	695368-001	3/29/2013	753	0.4	0	301.2	3
55	692783-001	3/29/2013	1006	0.6	0	603.6	3
55	695347-002	3/29/2013	45	0.6	0	27	3
55	693633-001	3/29/2013	9	0.8	0	7.2	3
55	695369-003	3/29/2013	406	0.9	0	365.4	3
56	695264-002	4/1/2013	5	0.6	0	3	4
10	692782-007	4/1/2013	5	0.3	0	1.5	4
10	692777-002	4/1/2013	5	0.2	0	1	4
10	692776-002	4/1/2013	5	0.2	0	1	4
10	692771-001	4/1/2013	24	0.2	0	4.8	4
10	692776-002	4/1/2013	30	0.2	0	6	4
10	692775-002	4/1/2013	25	0.2	0	5	4
10	692771-002	4/1/2013	37	0.2	0	7.4	4
10	692778-002	4/1/2013	25	0.2	0	5	4
10	695221-002	4/1/2013	10	0.2	0	2	4
10	692780-001	4/1/2013	10	0.2	0	2	4
10	692779-002	4/1/2013	10	0.2	0	2	4
10	692770-001	4/1/2013	4	0.2	0	0.8	4
10	692771-002	4/1/2013	13	0.2	0	2.6	4
10	692772-002	4/1/2013	25	0.2	0	5	4
10	688532-002	4/1/2013	5	0.3	0	1.5	4
55	693432-002	4/1/2013	71	0.9	0	63.9	4
55	694990-002	4/1/2013	2	1.6	0	3.2	4
55	694847-002	4/1/2013	46	0.6	0	27.6	4
55	694850-002	4/1/2013	50	0.6	0	30	4
55	693853-001	4/1/2013	251	0.6	0	150.6	4
55	694853-001	4/1/2013	39	0.4	0	15.6	4
55	694676-001	4/1/2013	4	0.6	0	2.4	4
55	695136-001	4/1/2013	1	0.8	0	0.8	4
55	680230-002	4/1/2013	80	0.9	0	72	4
55	693646-002	4/1/2013	5	0.9	0	4.5	4
55	693856-001	4/1/2013	1	0.8	0	0.8	4
55	692570-002	4/1/2013	409	0.6	0	245.4	4
55	695228-002	4/1/2013	10	0.6	0	6	4
55	695424-001	4/1/2013	7	0.4	0	2.8	4
55	692463-001	4/1/2013	12	0.6	0	7.2	4
55	693850-001	4/1/2013	101	0.4	0	40.4	4
55	693856-004	4/1/2013	10	0.6	0	6	4
55	695495-001	4/1/2013	11	0.4	0	4.4	4
55	693799-002	4/1/2013	300	0.9	0	270	4
55	693799-002	4/1/2013	300	0.9	0	270	4
55	693649-002	4/1/2013	1	1.6	0	1.6	4
55	692811-002	4/1/2013	1	0.8	0	0.8	4
55	693856-005	4/1/2013	21	0.6	0	12.6	4
55	693856-002	4/1/2013	20	0.4	0	8	4
55	695242-004	4/1/2013	5	0.4	0	2	4
55	694847-003	4/1/2013	46	0.6	0	27.6	4
55	693079-002	4/1/2013	7	0.6	0	4.2	4

<tbody>
</tbody>

 

[dandungan]

I was able to stick with the original plan. The autofilter wasn't working because the data from the textbox was the wrong data type. So I created a variable of the correct data type and autofilter completed.