Is it possible to print multiples of same form in one PDF?

[Harry Geda]

Hello,

I have a form in Excel that has a "print then increment" button.
After printing it increments the number in C1.
For 40 pages of print I do the following process 40 times:
1- click the button.
2- rename the PDF generator file name to the next number.
3- Wait for print to complete.
Repeat from number 1.

Then I combine the files from 01 to 039 to form a single file.

Is it possible to specify the count of 40 and print them all in same PDF file by one click?

 

[djreiswig]

I was trying to accomplish something similar a while back. I ended up making a temporary sheet and then copying the sheet I wanted to print and changing the data for each page and printing the whole sheet at once. That is the only way I could figure out how to get it to work.

 

[Harry Geda]

Hello djreiswig,

Interesting approach. It never crossed my mind.
Unfortunately my pages are more complicated than I explained.

Doing what you suggested for my project will be a long task and it will be full of errors for me since I fuss with it for a long time brfore I know what I want. Then creating 40 sheets and copying across the sheets with the equations beeing linked to data in sheet 1 is tough.
And occasionaly I get an 85 or 170 page project(s).

Even then I have to come back and do some changes.
Keeping track of 40 double sided prints is no fun.

I will keep that in mind for smaller projects.

Thank you,
Harry

 

[djreiswig]

So you are changing more than one piece of data each time you print?

I'm sure you could accomplish what you want with a macro. You wouldn't need to copy the formulas to the temporary sheet, just the values.

Not sure what you mean by double sided. I thought you were printing to a pdf.

 

[Harry Geda]

Hi djreiswig,

I can not do the multiple sheet approach.
My data can not be scattered in other pages.
It has to be all on one sheet.
40 pages of front and 40 of the back is not managable.
Each form of mine has a reverse side also with changing numbers.

So each PDF print is made of 2 pages. Front and Back.

Sorry if I simplified it in the question. I figured once I learn what to do with front page
then I can add to it on my own.

Regards,
Harry

 

[djreiswig]

I don't understand what exactly you are doing. You are trying to end up with a pdf with page 1 front page 1 back, page 2 front, page 2 back, page 3 front, page 3 back...to page 40. Each of these pages has something different on it.

Is the basic structure of each of the sheets the same, just some data is different? I don't see why you can't come up with a final sheet with all 40 front and backs in order (80 pages) and then print to a pdf one time. If you have to change the data manually on each set of pages, then you could make your button copy the data to the next spot in the temporary sheet and then reset for the next set of changes. Then when you get done, you can have another button that prints the temporary worksheet.

Can you post an example of your sheet, or upload a copy so I can see what you are working with. Or PM me and I'll give you my e-mail. You can make a dummy sheet if you don't want to send your actual data.

I think that if you can do what you want manually, it should be able to be automated.

 

[Harry Geda]

djreiswig,

Unfortunately I can not share my form.
I liked learning your way of doing things but I refuse
to make an excel file with so many sheets.
While the project on hand was 40 page pairs in some other
cases my projects it will be 80 page pairs.
The data on every 80 page has to be refferenced somewhere
to sheet1.
40 to 80 times of pressing a button is not as hard compared
to copying into 40 to 80 sheets.
Then one mistake in formatting has to be corrected in 5 places
per page and then 40 to 80 sheets since the basic formula is
not common either.
The data must be collected from sheet1 for each 5 sections of 40 pages.
There is a basic form with so many cells of calculated and pulled data.
Also there is conditional color formatting for different sections
of the page that does not repeat.
You probably know how painful is to change something for
"conditional formatting".

Regards,
Harry

 

[djreiswig]

You wouldn't have that many sheets. The way I am talking about, you would only have one additional sheet. It would have a page (or 2) for each version of your sheet. Each time you set up your sheet, you would "print" it to the temporary sheet, and then when you are done with all of your printing, you could just print the temporary sheet to a pdf file.

I have a program with something very similar to this going on. I wanted to print several copies of a single sheet, but each one is a little different. If I printed each one after I modified it, each one came out as a separate print job. I made a single sheet that I build my print job to and then when I have all of the pages in that sheet then I print it. It only sends one job to the printer.

Do you just have one sheet that you are changing before printing it each time? How many pages are in this sheet when it is printed? I'm assuming 2 since you say front and back.

This is how I see it should work. You set up the sheet1 like you want it. The macro then copies it to the temporary printing sheet. Then you set sheet1 up again for the second printing. The macro then copies it to the temporary printing sheet below what was previously copied. This continues until you are done. Then you can go to the temporary printing sheet and print/save it as a pdf. You can then clear/delete the temporary sheet.

As for the formula referencing, you would just copy the values and formatting so there would be no references.

 

[Harry Geda]

Hello,

I do not understand the temporary printing concept.
Please explain.

Thank you,
Harry

 

[djreiswig]

You build one sheet with all of the things you want to print, and then you print the one sheet. As I explained above, you set up your sheet1 the way you want it, then you copy the data to another sheet, then you can start over with sheet1 and set it up again with the next set of data. Then you can copy the new data to the second sheet below the previously copied data. Keep doing this until you are done modifying the sheet1 and then you will have on the second sheet everything you wanted to print all in one place. You can make this into your pdf and it will have all of the different pages all in one place and in the correct order.

The copying should all be able to be done with VBA.