Exporting Data From Multiple Workbooks to One Workbook

[samsilverman]

I do environmental testing for multiple hospitals and surgery centers. I've created a master workbook in excel 2007 that includes about 7 sheets. Each sheet is for account info, testing areas, billing, and reports. In the testing areas sheet, I've used a formula to compute in column F a return date for each row based on when the area in that row was tested AND based on that area's yearly schedule (quarterly, semi-annually or annually). The date tested info is in column E of each row and the yearly schedule is in column H of each row.

Every client has their own workbook created from the master workbook template. I would like to export the return dates data in column F of each workbook into a single workbook that would tell me what testing I have to do for each client in any given month so I don't have to go to each workbook and make a list manually. So, not only the date would have to show, but also all the other info in that row (acct number, location, charge, etc). BTW, the data in this testing areas sheet I want info from is not in a "table"...just in cells.

Any ideas? ray: I'm a newbie who has learned a great deal in the last month about Excel, but still has a lot to learn. I can, however, follow instructions pretty well.

Thanks!!

 

[hiker95]

samsilverman,

Welcome to the MrExcel board.

Are all the workbooks, that you want to report on, in the same folder?

If so, what is the path to that folder?

What is the sheetname, in each of the workbooks, and range of the data that you want to copy to your report worksheet?

Please post a screenshot of your summary worksheet in your master file.

And, post a screenshot of one of your data workbooks, worksheet to copy from.

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4 (contains graphic instructions).
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

Or, with Colo's HTML Maker.
http://www.puremis.net/excel/downloads.shtml

 

[samsilverman]

"Are all the workbooks, that you want to report on, in the same folder?"

Clients are serviced in different months of each quarter. So, there would be a year folder (2010) with month sub-folders. Each month sub-folder would then have the clients who are serviced in that month represented by a workbook. Each workbook is formatted the same (from a master template), but has data for that client only.

So, let's say folder 2010 has month sub-folders Jan, Feb and Mar. Jan has client A. Feb has clients B, C, D and E. Mar has clients F, G, H, I, J. Each workbook would be named in similarly (clientname_master_MM.DD.YY). If it makes a difference, I could put all the workbooks in a single folder (2010) and name them "clientname_month_year." having them in month sub-folders just makes for quick referencing/look-ups when a client calls.

"If so, what is the path to that folder?"

The path would be C:\EDT\2010...(depending on sub-folders)

"What is the sheetname, in each of the workbooks, and range of the data that you want to copy to your report worksheet?"

The sheetname in each workbook is Test Areas (yes, there is a space...but that can change if needed). The range is tricky. Every client has a different number of areas and chemicals tested. One client might have 10 rows of data. Another might have 40 rows of data. Can I choose a range that would surpass the maximum number of rows any client might have? For instance, if my biggest client has 40 rows of data, can I make the range 50 rows? If so, can the extra rows be blank or do they need to at least have some benign filler data in them? Like dashes, N/A, etc?

"Please post a screenshot of your summary worksheet in your master file."

I don't know exactly what the summary workbook/sheet would look like right now. But here's a guess:

Excel Workbook
	A	B	C	D	E	F	G	H
1
2		Work for month of
3
4
5		Account	Facility Name	Location	Hazardous Chemical	Next Date	Next Charge
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
TEST AREAS

If I had my wish, cell C2 would contain a list of months so that when I select, say, March, the data would fill in from the various workbooks where I have my next visit to the client scheduled as March. You know, something incredibly awesome like that. Oh, and I would probably want to formula column C to return a client's name based on their account number in column B. But, I think I can figure that one out.

"And, post a screenshot of one of your data workbooks, worksheet to copy from."

Excel Workbook
	A	B	C	D	E	F	G	H	I
1				These page are for internal information only - DO NOT PRINT
2
3
4
5		Account	Location	Hazardous Chemical	Current Date	Next Date	Next Charge	Yearly Schedule
6		0238A-1	PROCEDURE ROOMS	AIRFLOWS (ROOM)	Aug-18-09	Feb-2010	$200.00	SEMI-ANNUAL
7		0238F-1	SUBSTERILE 1-2	FORMALDEHYDE	Aug-18-09	Aug-2010	$175.00	ANNUAL
8		0238F-2	SUBSTERILE 3-MINOR	FORMALDEHYDE	Aug-18-09	Aug-2010	$175.00	ANNUAL
9		0238F-3	PROCEDURE ROOM 2	FORMALDEHYDE	Aug-18-09	Aug-2010	$175.00	ANNUAL
10		0238HYD-1	CLEAN UTILITY	HYDROGEN PEROXIDE	Aug-18-09	Feb-2010	$500.00	SEMI-ANNUAL
11		0238MA-1	ENTIRE FACILITY	MEDICAL PIPING SYS	Aug-18-09	Aug-2010	$500.00	ANNUAL
12		0238N-1	SURGERY ROOMS	NITROUS OXIDE	Nov-02-09	Feb-2010	$600.00	QUARTERLY
13		0238PE-1	CLEAN UTILITY	PERACETIC ACID	Aug-18-09	Aug-2010	$175.00	ANNUAL
14		0238VOC-1	DECONTAMINATION	VOLATILE ORGANIC COM	Aug-18-09	Aug-2010	$175.00	ANNUAL
15		0238VOC-2	PROCEDURE ROOMS 1 & 2 SUBS	VOLATILE ORGANIC COM	Aug-18-09	Aug-2010	$175.00	ANNUAL
16
17
18
TEST AREAS

Column F has a formula to calculate the return date to the client based on columns E and H. So, in the screenshot above, I would want to pull each row's info for a return date of Feb 2010. But, I would want that for each client for Feb 2010 to post to a master workbook/sheet.

I hope I have explained it well enough. I'm certainly open to more questions. Thanks for your help.

sam

 

[hiker95]

samsilverman,

Three sample data files per your example (in C:\TestData\2010\Aug for testing):

Excel Workbook
	B	C	D	E	F	G	H
1	*	*	These page are for internal information only - DO NOT PRINT
2	*	*	*	*	*	*	*
3	*	samsilverman1_master_08.18.09.xlsx	*	*	*	*	*
4	*	*	*	*	*	*	*
5	Account	Location	Hazardous Chemical	Current Date	Next Date	Next Charge	Yearly Schedule
6	0238A-1	PROCEDURE ROOMS	AIRFLOWS (ROOM)	Aug-18-09	Feb-2010	$200.00	SEMI-ANNUAL
7	0238F-1	SUBSTERILE 1-2	FORMALDEHYDE	Aug-18-09	Aug-2010	$175.00	ANNUAL
8	*	*	*	*	*	*	*
TEST AREAS

Excel Workbook
	B	C	D	E	F	G	H
1	*	*	These page are for internal information only - DO NOT PRINT
2	*	*	*	*	*	*	*
3	*	samsilverman2_master_08.18.09.xlsx	*	*	*	*	*
4	*	*	*	*	*	*	*
5	Account	Location	Hazardous Chemical	Current Date	Next Date	Next Charge	Yearly Schedule
6	0238F-2	SUBSTERILE 3-MINOR	FORMALDEHYDE	Aug-18-09	Aug-2010	$175.00	ANNUAL
7	0238F-3	PROCEDURE ROOM 2	FORMALDEHYDE	Aug-18-09	Aug-2010	$175.00	ANNUAL
8	0238HYD-1	CLEAN UTILITY	HYDROGEN PEROXIDE	Aug-18-09	Feb-2010	$500.00	SEMI-ANNUAL
9	*	*	*	*	*	*	*
TEST AREAS

Excel Workbook
	B	C	D	E	F	G	H
1	*	*	These page are for internal information only - DO NOT PRINT
2	*	*	*	*	*	*	*
3	*	samsilverman3_master_08.18.09.xlsx	*	*	*	*	*
4	*	*	*	*	*	*	*
5	Account	Location	Hazardous Chemical	Current Date	Next Date	Next Charge	Yearly Schedule
6	0238MA-1	ENTIRE FACILITY	MEDICAL PIPING SYS	Aug-18-09	Aug-2010	$500.00	ANNUAL
7	0238N-1	SURGERY ROOMS	NITROUS OXIDE	Aug-18-09	Nov-2009	$600.00	QUARTERLY
8	0238PE-1	CLEAN UTILITY	PERACETIC ACID	Aug-18-09	Aug-2010	$175.00	ANNUAL
9	0238VOC-1	DECONTAMINATION	VOLATILE ORGANIC COM	Aug-18-09	Aug-2010	$175.00	ANNUAL
10	0238VOC-2	PROCEDURE ROOMS 1 & 2 SUBS	VOLATILE ORGANIC COM	Aug-18-09	Aug-2010	$175.00	ANNUAL
11	*	*	*	*	*	*	*
TEST AREAS

Sample "summary workbook/sheet" before the macro:

Excel Workbook
	B	C	D	E	F	G	H
1	*	*	*	*	*	*	*
2	Work for month of	Work for Year of	*	*	*	*	*
3	Aug	2010	*	*	*	*	*
4	*	*	*	*	*	*	*
5	Account	Facility Name	Location	Hazardous Chemical	Next Date	Next Charge	Filename
6	*	*	*	*	*	*	*
7	*	*	*	*	*	*	*
8	*	*	*	*	*	*	*
9	*	*	*	*	*	*	*
10	*	*	*	*	*	*	*
11	*	*	*	*	*	*	*
12	*	*	*	*	*	*	*
13	*	*	*	*	*	*	*
14	*	*	*	*	*	*	*
15	*	*	*	*	*	*	*
16	*	*	*	*	*	*	*
TEST AREAS

Sample "summary workbook/sheet" after the macro:

Excel Workbook
	B	C	D	E	F	G	H
1	*	*	*	*	*	*	*
2	Work for month of	Work for Year of	*	*	*	*	*
3	Aug	2010	*	*	*	*	*
4	*	*	*	*	*	*	*
5	Account	Facility Name	Location	Hazardous Chemical	Next Date	Next Charge	Filename
6	0238A-1	PROCEDURE ROOMS	AIRFLOWS (ROOM)	Aug-18-09	Feb-18-10	$200.00	samsilverman1_master_08.18.09.xlsx
7	0238F-1	SUBSTERILE 1-2	FORMALDEHYDE	Aug-18-09	Aug-18-10	$175.00	samsilverman1_master_08.18.09.xlsx
8	0238F-2	SUBSTERILE 3-MINOR	FORMALDEHYDE	Aug-18-09	Aug-18-10	$175.00	samsilverman2_master_08.18.09.xlsx
9	0238F-3	PROCEDURE ROOM 2	FORMALDEHYDE	Aug-18-09	Aug-18-10	$175.00	samsilverman2_master_08.18.09.xlsx
10	0238HYD-1	CLEAN UTILITY	HYDROGEN PEROXIDE	Aug-18-09	Feb-18-10	$500.00	samsilverman2_master_08.18.09.xlsx
11	0238MA-1	ENTIRE FACILITY	MEDICAL PIPING SYS	Aug-18-09	Aug-18-10	$500.00	samsilverman3_master_08.18.09.xlsx
12	0238N-1	SURGERY ROOMS	NITROUS OXIDE	Aug-18-09	Nov-18-09	$600.00	samsilverman3_master_08.18.09.xlsx
13	0238PE-1	CLEAN UTILITY	PERACETIC ACID	Aug-18-09	Aug-18-10	$175.00	samsilverman3_master_08.18.09.xlsx
14	0238VOC-1	DECONTAMINATION	VOLATILE ORGANIC COM	Aug-18-09	Aug-18-10	$175.00	samsilverman3_master_08.18.09.xlsx
15	0238VOC-2	PROCEDURE ROOMS 1 & 2 SUBS	VOLATILE ORGANIC COM	Aug-18-09	Aug-18-10	$175.00	samsilverman3_master_08.18.09.xlsx
16	*	*	*	*	*	*	*
TEST AREAS

In the last screenshot, cells B3 and C3 could be data validation lists for Month (Jan, ... Aug,...Dec) and Year (2009,...2010,...).


The macro would check the above two cells, and if either were blank, would give you a message to make a correct choice.

After you make correct choices, the macro would check to see that the folder YEAR\MONTH did exist and that there was *.xlsx files in the folder.

If there were *.xlsx files in the folder, the macro would open copy close each workbook.

 

[hiker95]

samsilverman,

Here we go one more time.

Three sample data files per your example (in C:\TestData\2010\Aug for testing):

Excel Workbook
	B	C	D	E	F	G	H
2
3		samsilverman1_master_08.18.09.xlsx
4
5	Account	Location	Hazardous Chemical	Current Date	Next Date	Next Charge	Yearly Schedule
6	0238A-1	PROCEDURE ROOMS	AIRFLOWS (ROOM)	Aug-18-09	Feb-2010	$200.00	SEMI-ANNUAL
7	0238F-1	SUBSTERILE 1-2	FORMALDEHYDE	Aug-18-09	Aug-2010	$175.00	ANNUAL
8
TEST AREAS

Excel Workbook
	B	C	D	E	F	G	H
2
3		samsilverman2_master_08.18.09.xlsx
4
5	Account	Location	Hazardous Chemical	Current Date	Next Date	Next Charge	Yearly Schedule
6	0238F-2	SUBSTERILE 3-MINOR	FORMALDEHYDE	Aug-18-09	Aug-2010	$175.00	ANNUAL
7	0238F-3	PROCEDURE ROOM 2	FORMALDEHYDE	Aug-18-09	Aug-2010	$175.00	ANNUAL
8	0238HYD-1	CLEAN UTILITY	HYDROGEN PEROXIDE	Aug-18-09	Feb-2010	$500.00	SEMI-ANNUAL
9
TEST AREAS

Excel Workbook
	B	C	D	E	F	G	H
2
3		samsilverman3_master_08.18.09.xlsx
4
5	Account	Location	Hazardous Chemical	Current Date	Next Date	Next Charge	Yearly Schedule
6	0238MA-1	ENTIRE FACILITY	MEDICAL PIPING SYS	Aug-18-09	Aug-2010	$500.00	ANNUAL
7	0238N-1	SURGERY ROOMS	NITROUS OXIDE	Aug-18-09	Nov-2009	$600.00	QUARTERLY
8	0238PE-1	CLEAN UTILITY	PERACETIC ACID	Aug-18-09	Aug-2010	$175.00	ANNUAL
9	0238VOC-1	DECONTAMINATION	VOLATILE ORGANIC COM	Aug-18-09	Aug-2010	$175.00	ANNUAL
10	0238VOC-2	PROCEDURE ROOMS 1 & 2 SUBS	VOLATILE ORGANIC COM	Aug-18-09	Aug-2010	$175.00	ANNUAL
11
TEST AREAS

Sample "summary workbook/sheet" before the macro:

Excel Workbook
	B	C	D	E	F	G	H
1
2	Work for month of	Work for Year of
3	Aug	2010
4
5	Account	Facility Name	Location	Hazardous Chemical	Next Date	Next Charge	Filename
6
7
8
9
10
11
12
13
14
15
16
TEST AREAS

Sample "summary workbook/sheet" after the macro:

Excel Workbook
	B	C	D	E	F	G	H
1
2	Work for month of	Work for Year of
3	Aug	2010
4
5	Account	Facility Name	Location	Hazardous Chemical	Next Date	Next Charge	Filename
6	0238A-1	PROCEDURE ROOMS	AIRFLOWS (ROOM)	Aug-18-09	Feb-18-10	$200.00	samsilverman1_master_08.18.09.xlsx
7	0238F-1	SUBSTERILE 1-2	FORMALDEHYDE	Aug-18-09	Aug-18-10	$175.00	samsilverman1_master_08.18.09.xlsx
8	0238F-2	SUBSTERILE 3-MINOR	FORMALDEHYDE	Aug-18-09	Aug-18-10	$175.00	samsilverman2_master_08.18.09.xlsx
9	0238F-3	PROCEDURE ROOM 2	FORMALDEHYDE	Aug-18-09	Aug-18-10	$175.00	samsilverman2_master_08.18.09.xlsx
10	0238HYD-1	CLEAN UTILITY	HYDROGEN PEROXIDE	Aug-18-09	Feb-18-10	$500.00	samsilverman2_master_08.18.09.xlsx
11	0238MA-1	ENTIRE FACILITY	MEDICAL PIPING SYS	Aug-18-09	Aug-18-10	$500.00	samsilverman3_master_08.18.09.xlsx
12	0238N-1	SURGERY ROOMS	NITROUS OXIDE	Aug-18-09	Nov-18-09	$600.00	samsilverman3_master_08.18.09.xlsx
13	0238PE-1	CLEAN UTILITY	PERACETIC ACID	Aug-18-09	Aug-18-10	$175.00	samsilverman3_master_08.18.09.xlsx
14	0238VOC-1	DECONTAMINATION	VOLATILE ORGANIC COM	Aug-18-09	Aug-18-10	$175.00	samsilverman3_master_08.18.09.xlsx
15	0238VOC-2	PROCEDURE ROOMS 1 & 2 SUBS	VOLATILE ORGANIC COM	Aug-18-09	Aug-18-10	$175.00	samsilverman3_master_08.18.09.xlsx
16
TEST AREAS

In the last screenshot, cells B3 and C3 could be data validation lists for Month (Jan, ... Aug,...Dec) and Year (2009,...2010,...).


The macro would check the above two cells, and if either were blank, would give you a message to make a correct choice.

After you make correct choices, the macro would check to see that the folder YEAR\MONTH did exist and that there was *.xlsx files in the folder.

If there were *.xlsx files in the folder, the macro would open copy close each workbook.


Is this acceptable to you?

 

[samsilverman]

Thanks for your input Hiker. I really appreciate the attempt at help.

Just some clarification though. The folders (year, month) are when I service the client, not when I need to NEXT service the client. So, for instance, I am at a client's facility right now. The work I am doing would go into their workbook Clientname_11.10.09 in a folder C:\EDT\2009\NOV. That workbook would contain a worksheet with all the items I test at the facility (Testing Areas) where one column (F) contains the next date. So, ideally, I really need the macro to search through all workbooks in the EDT folder because there are clients I only service once a year. When I go out in December, I need to know what all is due in December even if the last time I serviced the client was a year ago. The macro would not need to look past a year though, so I could move older workbooks into another folder where they would not be part of the search (If that helped).

Also, it looks like the last screenshot you provided had some of the data in the wrong columns. Lastly, how do I write such a macro? Didn't see it in your reply. Please forgive my ignorance if it's embedded there somewhere. I have no experience with macros, btw.

Thanks again, so much, for your attention. If you can provide any additional assistance, I would appreciate it greatly. Will check back for a reply later today, as I am on the road.

 

[hiker95]

samsilverman,

it looks like the last screenshot you provided had some of the data in the wrong columns.

Follow this to see if the data is now correct.

The scenario; it is now Aug 2010.

If I have in folder: C:\TestData\2009\Aug
samsilverman1_master_08.18.09.xlsx
samsilverman2_master_08.18.09.xlsx
samsilverman3_master_08.18.09.xlsx

Which stand for clients:
samsilverman1
samsilverman2
samsilverman3



Sample data in C:\TestData\2009\Aug

Excel Workbook
	B	C	D	E	F	G	H
3		samsilverman1_master_08.18.09.xlsx
4
5	Account	Location	Hazardous Chemical	Current Date	Next Date	Next Charge	Yearly Schedule
6	0238A-1	PROCEDURE ROOMS	AIRFLOWS (ROOM)	Aug-18-09	Feb-2010	$200.00	SEMI-ANNUAL
7	0238F-1	SUBSTERILE 1-2	FORMALDEHYDE	Aug-18-09	Aug-2010	$175.00	ANNUAL
8
TEST AREAS

Excel Workbook
	B	C	D	E	F	G	H
3		samsilverman2_master_08.18.09.xlsx
4
5	Account	Location	Hazardous Chemical	Current Date	Next Date	Next Charge	Yearly Schedule
6	0238F-2	SUBSTERILE 3-MINOR	FORMALDEHYDE	Aug-18-09	Aug-2010	$175.00	ANNUAL
7	0238F-3	PROCEDURE ROOM 2	FORMALDEHYDE	Aug-18-09	Aug-2010	$175.00	ANNUAL
8	0238HYD-1	CLEAN UTILITY	HYDROGEN PEROXIDE	Aug-18-09	Feb-2010	$500.00	SEMI-ANNUAL
9
TEST AREAS

Excel Workbook
	B	C	D	E	F	G	H
3		samsilverman3_master_08.18.09.xlsx
4
5	Account	Location	Hazardous Chemical	Current Date	Next Date	Next Charge	Yearly Schedule
6	0238MA-1	ENTIRE FACILITY	MEDICAL PIPING SYS	Aug-18-09	Aug-2010	$500.00	ANNUAL
7	0238N-1	SURGERY ROOMS	NITROUS OXIDE	Nov-02-09	Feb-2010	$600.00	QUARTERLY
8	0238PE-1	CLEAN UTILITY	PERACETIC ACID	Aug-18-09	Aug-2010	$175.00	ANNUAL
9	0238VOC-1	DECONTAMINATION	VOLATILE ORGANIC COM	Aug-18-09	Aug-2010	$175.00	ANNUAL
10	0238VOC-2	PROCEDURE ROOMS 1 & 2 SUBS	VOLATILE ORGANIC COM	Aug-18-09	Aug-2010	$175.00	ANNUAL
11
TEST AREAS

Our summary workbook, worksheet before the macro:

Excel Workbook
	B	C	D	E	F	G	H
1
2	Work for month of	Work for Year of
3	Aug	2010
4
5	Account	Facility Name	Location	Hazardous Chemical	Next Date	Next Charge	Filename
6
7
8
9
10
11
12
13
14
15
16
TEST AREAS

Our summary workbook, worksheet AFTER the macro:

Excel Workbook
	B	C	D	E	F	G	H
1
2	Work for month of	Work for Year of
3	Aug	2010
4
5	Account	Facility Name	Location	Hazardous Chemical	Next Date	Next Charge	Filename
6	0238A-1	samsilverman1	PROCEDURE ROOMS	AIRFLOWS (ROOM)	Feb-18-10	$200.00	samsilverman1_master_08.18.09.xlsx
7	0238F-1	samsilverman1	SUBSTERILE 1-2	FORMALDEHYDE	Aug-18-10	$175.00	samsilverman1_master_08.18.09.xlsx
8	0238F-2	samsilverman2	SUBSTERILE 3-MINOR	FORMALDEHYDE	Aug-18-10	$175.00	samsilverman2_master_08.18.09.xlsx
9	0238F-3	samsilverman2	PROCEDURE ROOM 2	FORMALDEHYDE	Aug-18-10	$175.00	samsilverman2_master_08.18.09.xlsx
10	0238HYD-1	samsilverman2	CLEAN UTILITY	HYDROGEN PEROXIDE	Feb-18-10	$500.00	samsilverman2_master_08.18.09.xlsx
11	0238MA-1	samsilverman3	ENTIRE FACILITY	MEDICAL PIPING SYS	Aug-18-10	$500.00	samsilverman3_master_08.18.09.xlsx
12	0238N-1	samsilverman3	SURGERY ROOMS	NITROUS OXIDE	Feb-02-10	$600.00	samsilverman3_master_08.18.09.xlsx
13	0238PE-1	samsilverman3	CLEAN UTILITY	PERACETIC ACID	Aug-18-10	$175.00	samsilverman3_master_08.18.09.xlsx
14	0238VOC-1	samsilverman3	DECONTAMINATION	VOLATILE ORGANIC COM	Aug-18-10	$175.00	samsilverman3_master_08.18.09.xlsx
15	0238VOC-2	samsilverman3	PROCEDURE ROOMS 1 & 2 SUBS	VOLATILE ORGANIC COM	Aug-18-10	$175.00	samsilverman3_master_08.18.09.xlsx
16
TEST AREAS

Is the above summary data correct for this scenario?

 

[samsilverman]

It all looks right except a few items. One, the file location for all Aug 2009 clients would be: C:\EDT\2009\Aug

samsilverman1_master_08.18.09.xlsx
samsilverman2_master_08.18.09.xlsx
samsilverman3_master_08.18.09.xlsx

Which stand for clients:
samsilverman1
samsilverman2
samsilverman3

Two, the file names are fine. But they may not all have the same date in Aug.

All the supposed Sample Data looks correct, as does the summary workbook BEFORE the macro.

Three, here's how I think it should look after the macro. Notice the only data returned is that which is due for the month of (in this example) Aug 2010.

Excel Workbook
	B	C	D	E	F	G	H
1
2	Work for month of	Work for year of
3	AUG	2010
4
5	Account	Facility Name	Location	Hazardous Chemical	Next Date	Next Charge	Filename
6	0238F-1	samsilverman1	SUBSTERILE 1-2	FORMALDEHYDE	Aug-2010	$175.00	samsilverman1_master_08.18.09.xlsx
7	0238F-2	samsilverman2	SUBSTERILE 3-MINOR	FORMALDEHYDE	Aug-2010	$175.00	samsilverman2_master_08.18.09.xlsx
8	0238F-3	samsilverman2	PROCEDURE ROOM 2	FORMALDEHYDE	Aug-2010	$175.00	samsilverman2_master_08.18.09.xlsx
9	0238MA-1	samsilverman3	ENTIRE FACILITY	MEDICAL PIPING SYS	Aug-2010	$500.00	samsilverman3_master_08.18.09.xlsx
10	0238PE-1	samsilverman3	CLEAN UTILITY	PERACETIC ACID	Aug-2010	$175.00	samsilverman3_master_08.18.09.xlsx
11	0238VOC-1	samsilverman3	DECONTAMINATION	VOLATILE ORGANIC COM	Aug-2010	$175.00	samsilverman3_master_08.18.09.xlsx
12	0238VOC-2	samsilverman3	PROCEDURE ROOMS 1 & 2 SUBS	VOLATILE ORGANIC COM	Aug-2010	$175.00	samsilverman3_master_08.18.09.xlsx
13
MONTHLY DUE

Compare the one I pasted above to the one you did.


Our summary workbook, worksheet AFTER the macro:

Excel Workbook
	B	C	D	E	F	G	H
1
2	Work for month of	Work for Year of
3	Aug	2010
4
5	Account	Facility Name	Location	Hazardous Chemical	Next Date	Next Charge	Filename
6	0238A-1	samsilverman1	PROCEDURE ROOMS	AIRFLOWS (ROOM)	Feb-18-10	$200.00	samsilverman1_master_08.18.09.xlsx
7	0238F-1	samsilverman1	SUBSTERILE 1-2	FORMALDEHYDE	Aug-18-10	$175.00	samsilverman1_master_08.18.09.xlsx
8	0238F-2	samsilverman2	SUBSTERILE 3-MINOR	FORMALDEHYDE	Aug-18-10	$175.00	samsilverman2_master_08.18.09.xlsx
9	0238F-3	samsilverman2	PROCEDURE ROOM 2	FORMALDEHYDE	Aug-18-10	$175.00	samsilverman2_master_08.18.09.xlsx
10	0238HYD-1	samsilverman2	CLEAN UTILITY	HYDROGEN PEROXIDE	Feb-18-10	$500.00	samsilverman2_master_08.18.09.xlsx
11	0238MA-1	samsilverman3	ENTIRE FACILITY	MEDICAL PIPING SYS	Aug-18-10	$500.00	samsilverman3_master_08.18.09.xlsx
12	0238N-1	samsilverman3	SURGERY ROOMS	NITROUS OXIDE	Feb-02-10	$600.00	samsilverman3_master_08.18.09.xlsx
13	0238PE-1	samsilverman3	CLEAN UTILITY	PERACETIC ACID	Aug-18-10	$175.00	samsilverman3_master_08.18.09.xlsx
14	0238VOC-1	samsilverman3	DECONTAMINATION	VOLATILE ORGANIC COM	Aug-18-10	$175.00	samsilverman3_master_08.18.09.xlsx
15	0238VOC-2	samsilverman3	PROCEDURE ROOMS 1 & 2 SUBS	VOLATILE ORGANIC COM	Aug-18-10	$175.00	samsilverman3_master_08.18.09.xlsx
16
TEST AREAS

Again, a thousand thanks for being so patient and helpful. I hope this latest info I've provided is helpful.

sam

 

[Nimit]

Excellent solution, as usual, Hiker95 !! My inspiration to learn and help !!

 

[hiker95]

Nimit, thank you very much.


samsilverman,

It all looks right except a few items. One, the file location for all Aug 2009 clients would be: C:\EDT\2009\Aug

For all my testing I use "C:\TestData".

For your final product if will work with "C:\EDT".

For 2010, you will have to enter in B3 "Aug", and C2 "2010", and the macro will subtract 1 from 2010 and search in "C:\EDT\2009\Aug".

I will be back later with a solution to remove all dates in the output that do not match "Aug".