Excel 2019-Batch Printing (?) Selective Sheets in a Spreadsheet with over 50 Sheets (tabs)

[CayukChris]

I am General Contractor and built a spreadsheet to estimate my projects. It was built as follows: The first sheet is a Main Summary. Behind that there are over 50 separate detail sheets (tabs) which correspond to different types of work on the project such as Concrete, Carpentry, Painting, Electrical, etc.. The sum total on each sheet then transfers to a specific line on the 1st Main Summary Sheet.


On a typical project that I estimate, I will use somewhere between 15 to 20 different detail sheets. The rest will remain blank with a value of (0).

What I would like to do is to "automate" the printing process (batch?) so that I only have to click one button or key to print all 15 to 20 detail sheets and the Main Summary Sheet. Each project that I estimate is unique so I may be using a different 15 to 20 sheets on different projects.

My idea is to have a radio box or fill-in cell on each detail sheet that I could click on so that when I hit the Print button it automatically prints the sheets "clicked on". Or maybe some kind of Statistical Function on the "Total" cell that would tell Excel to print that detail sheet if the total of that cell was greater than (0).. I am a beginner Excel user and hope someone can help me as I don't know how to accomplish this.

[jolivanes]

https://www.excelforum.com/excel-pro...ets-array.html

In Post #8 in above thread there is a workbook attached. It is set to export all selected sheets to a single PDF file but it will be easy to change to print individual files to a printer.
See if that would suit your need.
If so, let us know which changes need to be made.


Added:

Re: "The rest will remain blank with a value of (0)"
Are all these values on all sheets in the same cell, i.e. Cell J45?
If not, is there a value left of these cells that does not appear anywhere else in the sheet like "Total for this work:"
In other words, is there a way of finding the cell with the zero or non zero value in each sheet.

[CayukChris]

Thank You for replying to my post. I looked at the workbook in the other post and forgive me, but I don't understand how it works (see my statement above "I am a beginner Excel user"). The same month is listed in all the cells of any given sheet.

In response to your Added questions: It's sometimes hard to explain how specific methods are used to estimate construction projects, but to simplify, Yes, typically There is a column for Labor Costs, another for Material Costs, and another for Other Costs. These totals then all transfer to the 1st page Main Summary. That is why I was thinking of having a "radio button" or a cell with some kind of statistical function that would "as an example" if Cell J45=0, then do nothing. If Cell J45=(a non-zero number), then print the sheet with the other"selected sheets" at one time when the print command is hit. That way I could work on all of the needed sheets for this estimate and when I'm finished entering data, then print it all at one time.

A friend built this spreadsheet for me, and I'm still trying to learn it (as well as Excel) before I fully incorporate it into my business.

[jolivanes]

It sounds like the workbook mentioned is too complicated for now. No problem.
We don't need to know on how you arrived at the costs of each project but there must be a way of determining which sheets to print.
As you previously mentioned, not all sheets need printing.
The reason I asked about something like "Total for this work" is that the macro can find that particular text and see if the cell to the right, or whatever direction, has a value more than 0 (zero) which should mean that you want it printed.
The macro to print these sheets is no problem at all but there must be a way to determine which sheets need printing.
You can mark the sheets with a text or value somewhere in each sheet to be printed and the code can work on that.
You can have every sheet name to be printed in a column in your "Master" sheet or have all the sheet names in a column and an "x" beside the sheets that need printing.
Or whatever you want all the sheets to have in common that the macro can use to determine if it should be printed or not.
Play around with this attached workbook. Maybe something comes close to what you have in mind.

[CayukChris]

I've had a chance to look at the sample you sent me. Thank You!

If necessary, I can change the workbook sheets to sum the two columns into one cell for determining where to print. Or could we just add a cell, that I can insert either 1 or 0 for determining whether to print that sheet or not.

I think for me, the easiest method would be your example with the red box upper left "Print "Yes" sheets. It seems like the quickest method as I don't have to list all the sheets on every sheet (there are over 100). I played around with your sample. The "Yes" box gave me a run-time error 13 and the other boxes started printing the "x" sheets immediately when I clicked them, which I don't want it to do. What I'm hoping to accomplish, is to print the "selected" sheets when I'm done working on all the sheets of the estimate; not start printing immediately when I click on the button on any given sheet.

Would it be simpler to use a radio (option) button that I click on instead of the "yes" button.It seems to me that the "black dot" inside the cell would be easier to recognize looking at the sheet, unless the "Yes" button changes color (?) when its depressed ( or somehow "highlights"). Do you think this would work with the macro your proposing? Estimate Bid Days can be pretty hectic as they usually have a time deadline to submit so I'm usually multi-tasking which might cause me to miss things.Any visual "ques" would be helpful.

Once again, I want to say how much I appreciate your help! Do you want me to post my workbook for you to look at while we're working thru this?

[jolivanes]

We don't know what your workbook looks like so we're just guessing now.

Is easier to select a bunch of sheets out of a possible 100 or more?
It also sounds like you want to visible look at each sheet and decide than to print or not (inside the cell would be easier to recognize looking at the sheet)

"not start printing immediately" Don't know what this means if you're looking for a "Batch printing" macro.

Explain in a concise manner what needs to be done and attach a workbook without personal data like names, addresses, email addresses etc but with enough data and sheets to take some of the guesswork out.

[CayukChris]

I have attached the workbook below for your reference. I'm sorry for the confusion. I will try to clarify as follows.

The number of sheets overall is based on a standard Construction Specification Institute formatting system.Each sheet represents a different Cost Code for each "type" of material and the work performed to complete the project. As each project is unique with different materials, I will be using a different set of sheets accordingly.

My apologies on the confusion for "not start printing immediately" was poorly worded. Let me try to clarify. When I played around with the CayukChris sample workbook you posted, I clicked on the "print x marked sheets button" and the (x) sheets began to print immediately. I assumed that it will also do it in my workbook when I'm working on one of the sheets and I click the button. What I interpret "Batch Printing" to mean is and what I would like this workbook to do (if possible) is:
(1) I enter cost data on a specific sheet. When I've completed it, then I click on a "Yes Button" ,or a Radio (option) button, or put a non-zero number in a given cell linked to the macro, but it doesn't print the sheets at this time. then,
(2) I move on to the next sheet and repeat all of the steps from (1) above until I've repeated this for as many sheets as needed for the project.
(3) Finally, after all the sheets have been completed, I would like to hit a Print button somewhere (maybe on the Summary Sheet?) and the Summary Sheet + all of the specific sheets with data print in one "Batch".

[jolivanes]

The numbers 1, 2 and 3 in your last post do exactly that with the "x" Button AFTER you finish with all the sheets.
That is what should happen. You finish with all your sheets, check them as many times as you like and when all is to your satisfaction, click on the print button and it will print the sheets that you marked with an x, which should be the sheets that you worked on and want printed.
I had a quick look at your attachment and it has what I asked for previously. I assume that each Sheet that has an amount to the right of "TOTAL Materials:" and or to the right of "Labor $:" are the sheets that need printing. Right?
I'll go through it and follow up when finished.

[CayukChris]

Yes. For the sample workbook I posted, i added money to the following tabs:
01-005 Architectural Services
01-040 Preconstruction Services
01-100 Supervision
02-200 Earthwork

As I've been studying and learning this workbook, I discovered something that I didn't like & plan to change. Currently there are three "totals" cells (H9;H36;D38) on each sheet. These are then transferred to the appropriate line & cell on the Summary Sheet where the sum formula is located. I'm planning on summing the three cells on the sheet that they're located on & then that "Grand Total" cell will transfer to the appropriate location on the Summary Sheet. I think that would be much simpler than the way it is now.

[jolivanes]

This prints the "Summary" Sheet and all Sheets that have a Value for "Total Materials:" or/and for "Labor $:".

It does not print the last ("Detail") Sheet.
I have deleted the orphan "Sheet1" that somehow ended up in the middle of all your sheets.
It starts checking at the fifth sheet up to the last - 1 sheet ("Detail")
It is setup to print to a single PDF file into the folder where this workbook is saved so you won't waste paper. Or maybe keep it in PDF so you can check it and print from PDF.

Please Login or Register  to view this content.

I deleted some sheets at the end so it would not be over the maximum size allowed.
If you attach a workbook, do it without protected sheets etc.

[CayukChris]

Thank You again for all your hard work.