Print sheets based on filtered results from multiple workbooks

Hi everyone, I'm trying to add another feature to a macro that I have adapted. The base code comes from Ron De Bruin (with the necessary Mods) and works very well for what it is intended to do.
I would like to add a new feature to the code and have little success, in working it out, from numerous searches on the net.

Basically, the code searches workbooks in a folder and returns filtered results for every row that meets the criteria, Copying all those filtered results to a new workbook.

Each of these results has an associated "Detailed List" which contains more detailed information of the actual job.
As the filter pulls a result from a workbook, i would like the associated "Detailed List" to be printed.

The workbooks are all structured the same way:
Sheets that are being filtered are called "CHECKLIST". The checklist contains 17 rows of info (ie. different jobs).
The rest of the work book has sheets numbered 1-17 with more detailed info on each of these jobs.

At the moment I have a Print Button in each row, of the "CHECKLIST" that will print the desired sheet (1-17).

Is there a way, thru VBA, to link the main sheet cells with the respective detail sheet and then print the detail sheet when the "Filter Macro" produces/finds a result?
Maybe the code could activate the associated Print Sheet Macro when a positive result is found by the filter?????

I've attached a sample workbook with the macro code/s in it:


Regards,
Martin

mmmmmm. Am I wording the question wrong??? Is it not possible, what I ask????
No responses is very disheartening...

Here's the code anyways, if it helps get a response...

Please Login or Register  to view this content.

Hi Lungfish...could you put a marker or indicator in the spot of your code where you'd like to print? I'm not quite sure I understand what you're looking for. You may find the attached modified workbook interesting, though. I renamed each button and added a single macro run by each print button when clicked. Here's the code for that. It assumes each button is named "@btn" and the sheet number for which it affects. Ex. @btn1, @btn2 etc.

Please Login or Register  to view this content.

Thanks Greg.
The button click you added is cool, tidies up some of my crappy coding anyways!
The problem is, that once the new "Finish Off List" is generated, I have to go back through all the workbooks in the folder (20+ and growing weekly!) and then print each checklist that i want from the relevant workbook.

I was hoping that there would be someway, when a filter result is found, that the associated checklist could be printed straight away.

I have highlighted, in italics, the section of the code which finds the rows (according to search criteria) and then copies them into the new workbook.
I presume that any print code would have to go somewhere here, before the filter finishes searching and closes the workbook.

Maybe the @btn print function could be activated when a valid search result is found???

Regards

Martin

Hmmm...still not sure I'm getting it. I may need an example of a completed workbook (one that normally opens in the search/open portion of your code). I took a guess at what you're attempting...and maybe you'll be able to adapt it for what you're looking for if it doesn't quite work the way you'd like. This is just the With .AutoFilter.Range portion of your code above.

Please Login or Register  to view this content.

Hi again,
First things first. How do you rename the buttons to @btn??? That really puzzles me! Can the module be put in the "personal" workbook and run from there?
The modButtonClick Print code works really well. I just had to add : PrintFOC iSheetNum + 1 , because when I click on the first button it would print the first sheet. Obviously! But, the first sheet is the CHECKLIST, so the +1 fixed it. Now Button 1 prints sheet 2, button 2, sheet 3, etc. AWESOME!

I gave the filter code addittion a test run and it gives an error "438 : object not supported by this property or method" on the line of code:

Please Login or Register  to view this content.

I noticed in the code that iCount is 1 to 17. Should that be 2 to 18 because they're the sheets that I want to print?

I do apologize if I haven't explained my self very well so I'll give it another go.

Filter looks in column A for a result of less than 6 (being 6 weeks to go before the job is finished).

When it finds an agreeable result in a row (A3, A6, A9, A12.......A48, A51) it opens a new workbook with one sheet and adds the row to the new worksheet. (eg. if A9<6 then A9:W9 will be copied in its entirety to the new workbook/worksheet)

The filter then continues through "JOB CHECKLIST A", if it finds no results, closes, and opens "JOB CHECKLIST B" , filters, closes, opens, and so on for all files in the folder.

I believe that bit is already understood.

Where the checklist is concerned, the Job number is more or less irrelevant.
Job Numbers in Column B are linked to the Checklists like this:
Sheet 1 = CHECKLIST
Sheet 2 = Tab #1. This sheet is associated with B3 (Row 3)
Sheet 3 = Tab #2. This sheet is associated with B6 (Row 6)
Sheet 4 = Tab #3. This sheet is associated with B9 (Row 9)
.......
Sheet18 = Tab #17. This sheet is associated with B51 (Row 51)

Back to our previous example (and hopefully the part containing the explaination):
If the filter found "JOB CHECKLIST A" cell A9<6 then Sheet 4 should be printed.

If the filter found "JOB CHECKLIST B" cell A3<6 then Sheet 2 should be printed.

If the filter found "JOB CHECKLIST C" cell A51<6 then Sheet 18 should be printed.

I have attached 2 completed workbooks that hopefully will go a long way to clearing things up.

I quickly looked through them and there should be a total of 11 results found between the 2 workbooks (according to filter criteria).

Thanks for your time with this,

Regards,

Martin

Sorry about that...I must have been a little sleepy. There were at least 2 errors in that last code. Try this:

Please Login or Register  to view this content.

I'm at work now until around 6pm (and I'm guessing you'd be asleep right now anyway), but I'll check on this again this evening. Thanks!

Hi Lungfish...I missed the questions in your previous post. Sorry about that.

To name a button just click on it and then in the name box (to the left of the formulas box) and type in the name you want. I used @btn because in newer versions of Excel (2007 & 2010) BTN1 is a real cell reference. If you choose a more unique name for your buttons you wouldn't need the @ sign.

You can run the code from your personal workbook (by putting the code in your personal workbook and assigning the buttons to that macro), but that means only you will be able to run it...so don't ever get sick