VBA - Print worksheets Selection from a defined list.

[mark-cox]

Hi,

I have a large workbook 800+ Sheets and I need to print a selction of the 800 sheets, they are in a random order within the workbook so I cannnot easliy selected each sheet. I have a control sheet that is linked to each sheet by its sheet name I can filter the control sheet to select the 250 sheets that I need.
So is there a way that I could for example copy and paste the sheet names into a new sheet callec "print selection" and then use VBA to loop through the list to print each sheet.

the code would also need to adjust the print options to 1.) narrow margins, 2.) Fit all coulmns across one page.

Best Regards

Mark

[stnkynts]

I don't think you would need to copy and paste the sheet names to a new sheet and instead could just loop through the filtered range on the control sheet and print out based upon that. With regards to the narrow margins and Fit all columns you could just use the Macro Recorder to get the code. To give a direct answer I would need to examine the layout of the control sheet.

[mark-cox]

Hi Stnkynts

Thanks for your reply, sorry it has taken so long to get back to you, my work book is quite large so I have attached a smaller version with a control sheet and a selction of worksheets.
let me know if you need any more information.

[stnkynts]

What about the sheet names on the control.sheet determines whether they get printed out or not.

[mark-cox]

Hi,

New worksheets are created by VBA and the row in the control is created at the same time with links to the stock card "Worksheets" the product code "Sheet Name" is created in Cell E3 this is the same for all worksheets. I have a piece of VBA that changes the sheet name to match the entry in E3 of that Stock Card.
So unless a link is broken the entries in column C will match each Stock card exactly.
I would like to identify the list of sheets that need printing by filtering column B (their Stock location) so for example only print the "HARTRD" Stock cards, would print the 8 HARTRD stock cards Worksheets that are in the Sample workbook.

I hope this Helps & Thanks again for your time.

Best Regards

Mark

[stnkynts]

I still don't think you answered my question so I will make some assumptions. One of those is that you self filter for the values you want in column B.

Please Login or Register  to view this content.

[mark-cox]

Thanks Stnkynts

your code works a treat. but could you offer some advice as to where to add the print preferences into the code. I have used the Macro recorder and have the follwing Code

Please Login or Register  to view this content.

Sorry to be a pain and thanks again for your help.

Best Regards

Mark

[stnkynts]

Maybe this. Not sure though

Please Login or Register  to view this content.