Array to Find Workbook, Print Sheets from a List

I am posting this pseudo code in the hopes that someone may be able to actually flesh it out.
I had tried doing this myself but I'm having a hard time trying to set objects and work out the array.

In essence, I have a main file that contains a list of Workbook Names, their respective sheet names and the number of printed copies needed.
I wish to loop through the list,
save all the values to an array,
then open the directory folder,
Find the first workbook,
and print the sheets the required number of times.
Close that workbook and go through the rest of the array.


NOTE: The workbooks are not numbered as in the list example. They are all different names. Also this is for Excel 2007.

I've included the test workbook.
I haven't include the seven dummy workbooks that are listed.
If you'd like, I can upload them anytime.

Thanks in advance.

Please Login or Register  to view this content.

Mark

This is how I would do that:

Please Login or Register  to view this content.

I took the path variable out of the macro itself and placed it above, perhaps you didn't see that? I don't like burying constants inside a macro, it makes it less than obvious that it's a variable all macros can use. Putting them at the top makes it more clear, a personal preference. Make that string value whatever you need to make it work, I test on C:\2010\.

Yes, I put an END IF in the wrong place and it messed up the logic. Sorry about that. Here you go.

Please Login or Register  to view this content.

JB,

Thanks for pointing out that line. I had completely overlooked it.

Please Login or Register  to view this content.

In order to find that problem, I had stepped through the code and found it jumped out at the same location.

Please Login or Register  to view this content.

Although, I hadn't figured out exactly how to correct it.


Many thanks for your time.
This will save me lots of time.

I have a quick question regarding:

Please Login or Register  to view this content.

in the actual work file, there will be summation formulas that will give me the Print Quantity. Should I change this to xlCellTypeFormulas?

Thanks.

Originally Posted by max57

in the actual work file, there will be summation formulas that will give me the Print Quantity. Should I change this to xlCellTypeFormulas?

No, notice the range is column B only? We are making a range array of the workbook names, those are constants, yes?. The sheet names and quantities are being evaluated as an OFFSET from the workbook name.

==========
If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.