Print Worksheet(s) from selection on Drop Down

[johnnywinter]

I have a drop down in sheetQUOTE that selects rows from sheetSUMMARY- ( each row in sheetSUMMARY is the grand totals from a separate specific worksheet.)

Thanks to NBVC and Dean England for their help with the drop down.

MY ISSUE:
After sheetQUOTE is completed I would like a command button to push and
print

a) sheetQUOTE
b) each worksheet that is represented by a selection from the drop down.

I added a column in sheetSUMMARY and entered the worsksheet name for each row. I can get it selected in the drop down, but no success after that.

AND it would be great to have another command button to do same basic thing, except to save as one pdf (a) sheetQUOTE and each worksheet represented by a selection in the dropdown)

I have attached a scaled down XLs, just deleted all the detail on each worksheet- but grand totals still link to sheetSUMMARY

Thanks,

[protonLeah]

Attach the following procedure to a command button:

Please Login or Register  to view this content.

Note: A couple of tab names had extra spaces at the end which made the actual tab names different from the names in the list on the SUMMARY sheet. Also, the empty cells in the "Items" column on the QUOTE sheet had blank spaces and were not actually empty causing problems for the procedure as written. It does work and should get you started.

[johnnywinter]

proton Leah,

thanks. It looks exactly like what I need.


When I run the macro, I get an errror:

"Run Time error '9' Subscript out of range"-

and debugger yellow highlights "ReDim Preserve PrintList(SheetCount)"


I removed the blank spaces at end of the 2 sheet names and used Clear Contents on the blank cells in the "# Units" column. I have attached another copy of the cleaned up workbook with your macro, so you can see what it is doing.

As part of this, also need the macro to print: Worksheet(A. INDIVIDUAL PRODUCTS) if on Sheet(QUOTE) C:E33 > 0-- that is a worksheet that is not listed on sheet(SUMMARY) but that might have

Question: if the cells in "# Units" column have to be empty for this macro to work, will I have to force users to use "Clear Contents" or backspace out any entry-- or is there some other way to get what appears to be a blank cell into the empty state your macro will need?

[protonLeah]

I have modified the code as indicated. I tested it with and without entries in QUOTE sheet row 33. I also changed the test to account for blanks/spaces in the items column (the # Units was not part of the test). Hope this one works o.k.

[johnnywinter]

protonLeah, works really great. Thank you.

After everything is printed, ends up on the first sheet that listed in the rows on sheetQUOTES and leaves all the worksheets that were printed as activated (tabs highlighted).

I got it to return to sheetQUOTES by inserting:
"Worksheets("QUOTE").activate" as last line of your macro.

But, I do not know how to end up with sheet("QUOTES") being the only sheet activated.

Thanks again,

[johnnywinter]

I got it working,

just changed: "Worksheets("QUOTE").activate"

to: "Worksheets("QUOTE").select"

Again, thanks for all the help.