Print only certain worksheets?--Excel experts pls help

[cjones88]

I want to make a popup or some type of inquiry menu where it asks the user to specify which worksheets in the workbook to print. I have a data entry page, and four printable worksheets. Sometimes I only want to print two of the worksheets, but not the data page or the other two. Any way to do this besides printing the entire workbook or one worksheet at a time? It would be ideal if a popup menu came up with checkboxes for all of the available worksheets and the user could check off which ones to print.

 

[Datsmart]

Sarah,

You should not change the ".Name" portion of the code. That is refering to the NAME property of the sheet.
Your code has an apostrophe in front of the UserForm1.Show command. Remove that and try again.

 

[sarahrosenberg]

I made both corrections, and it looks like I am getting somewhere. But now when I click the button instead of doing nothing, I get an error that reads:
Run-time error'9':
Subscript out of range

What do you think I am doing wrong? I really appreciate your help on this!!

 

[Datsmart]

Is your userform in fact named, "UserForm1"?
Try commenting out the initialization code by putting apostrophes infront of those lines to see if they are causing your error. Then troubleshoot from there.

 

[sarahrosenberg]

It was named UserForm1 in one place, but UserForm in another. Thanks for the tip!

I was able to make my macro work when only printing one sheet, but when I try to print multiple sheets, I get an error. I am refering to my sheets by name; do you have any ideas what could be causing the error?

If CheckBox1.Value = True Then Sheets("Total Summary, VZ Total Summary").PrintOut Copies:=1

 

[sarahrosenberg]

Never mind...I changed it to an array and it worked. Thanks again for all your help!!

 

[sarahrosenberg]

The people I work with are NEVER satisfied! (Can any of you relate??) the person I am building this model for is complaining because my print macro sends the file to his default printer. Is there a way when the macro runs to have it bring up the menu that comes up when you hit File, Print? The menu that lets you select your printer?

Thanks again for all your help!

 

[Datsmart]

Use the code below to just bring up the Print Dialog box.

Application.Dialogs(xlDialogPrint).Show

 

[sarahrosenberg]

That brought up the print dialog screen, but when I selected my printer and clicked ok, nothing printed? Do I delete too much of the old command??

If CheckBox1.Value = True Then Sheets(Array("Total Summary", "VZ Total Summary")).Application.Dialogs(xlDialogPrint).Show

 

[marquiskissel]

This question is with regards to the print dialog box:
The following code pulls up the print dialog box, but when I press OK, it prints the page that my button for the macro is on and then it prints the sheets the macro is telling to print. How can I select what printer without printing the active sheet?

Application.Dialogs(xlDialogPrint).Show

Sheets(Array("Cover Page", "Enterprise Data", "Dash Board", "Per Share Metrics", "Profitability Trends", "VMC Profitability", "MLM Profitability", "FRK Profitability", "TXI Profitability", "RIN Profitability", "Balance Sheet", "ROCE (2)", "Dupont Analysis")).PrintOut Copies:=1

 

[Datsmart]

The easy way would be to make one of the sheets you are printing "Active" prior to calling the Print Dialog box.
Like;

Sheets("Cover Page").Select

Then you can remove "Cover Page" from the Sheets to print code so you don't get two copies.