Print to individual PDF files from list of Yes/No

**jkhuskies** · 06-23-2019, 11:39 PM

Hello,

I am rather new to VBA and having a good time browsing the forum finding code that seems to work well for the most part to fit my needs.

I have a spreadsheet with a large number of worksheets that I want printed to individual PDF files that correspond to the worksheet name. Currently my code will spit out all the "Yes" files to be printed in one single pdf.
I seem to be lost on how to get an if statement and a with statement to work together to print individual PDF files.

Please Login or Register  to view this content.

Any help would be greatly appreciated!

Thanks,
Jonathan

**TMS** · 06-24-2019, 02:04 AM

Untested, but maybe:

Please Login or Register  to view this content.

**Keebellah** · 06-24-2019, 02:06 AM

Have you thought of saving (exporting) as PDF, this might make it easier, you do not need an array and just save each (work)sheet as an individual PDF file.

Please Login or Register  to view this content.

Pass the worskheet name as parameter ...

**jkhuskies** · 06-24-2019, 09:09 AM

Hi Trevor,

The only complaint I have about this code you provided is I have to enter the name of the sheet in the save dialog box. I want it to pull the sheet name automatically.

I have code embedded in each worksheet (shown below) that is to be printed, this will print each individual worksheet by hand and uses the worksheet name.
This also makes it so that it doesn't print out to actual paper which is an issue for other people that have default settings as such.

Is there a way to incorporate something like this into the code you provided?
I did some testing and not sure how to do that.

Please Login or Register  to view this content.

Thank you,
Jonathan

**Keebellah** · 06-24-2019, 03:35 PM

Did you try mine?

**jkhuskies** · 06-25-2019, 09:41 AM

Hi Keebellah,

I got an ambiguous argument, and I was unable to run or assign to a macro button.

Not sure what is going on.

Thanks,
Jonathan

**Keebellah** · 06-25-2019, 10:11 AM

That means you’ve got two macros with the same name

**jkhuskies** · 06-25-2019, 10:42 AM

I looked and only one macro of each with unique name.
I even changed the name and removed the "public" just to see if it allows me to run the macro and I get nothing still.

**Keebellah** · 06-25-2019, 05:11 PM

Ambiguous means just that, you might have overlooked, open the VBA Editor press Ctrl + F en enter the macroname and make sure you sear the entire project (checkbox) it's there somewhere

**jkhuskies** · 06-25-2019, 05:34 PM

Thanks for the idea. I just don't see any other macro with the same name.

I have attached all my code below.

Attachment 629676

Please Login or Register  to view this content.

**TMS** · 06-25-2019, 05:47 PM

You said

I got an ambiguous argument

That's not the same as an ambiguous name.

**Keebellah** · 06-25-2019, 06:05 PM

True, but I suggest the file attached and see for ourselves.

**TMS** · 06-25-2019, 06:10 PM

No argument.

**jkhuskies** · 06-26-2019, 09:19 AM

Are you talking about your code you posted earlier?

Otherwise I don't see an attachment. I have attached a slimmed down version of my Excel file for reference.

I still can't get either code to do individual printouts.

**bakerman2** · 06-26-2019, 09:39 AM

Here's you're error. The 'e' isn't supposed to be there.

Please Login or Register  to view this content.

To print all sheets to individual pdf's this is all you need.

Please Login or Register  to view this content.

**jkhuskies** · 06-26-2019, 09:57 AM

I get a compile error "argument not optional".

I changed the following code and it runs now.

Please Login or Register  to view this content.

**jkhuskies** · 06-26-2019, 10:11 AM

Working code is below.

Please Login or Register  to view this content.

Solved

**bakerman2** · 06-26-2019, 11:21 AM

Glad you got it solved and thanks for rep+.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

**Keebellah** · 06-26-2019, 03:46 PM

Code works great just a note about the formulas, you should avoid #N/A add extra checks makes the sheet sloppy

**jkhuskies** · 07-01-2019, 10:37 AM

Hello Keebellah,

I now have a run-time error '5': Invalid procedure call or argument.
I even went back and tried the original code which doesn't work. I'm not sure why it's failing now.

Maybe you could take a look ?

Please Login or Register  to view this content.

Thanks,
Jonathan

**Keebellah** · 07-01-2019, 11:56 AM

No error here (of courese tested with another path) but maybe that's the problem.
Does the path exist?

**jkhuskies** · 07-01-2019, 12:40 PM

It's weird I have a test file and a completed file. The code works on the test file but not the completed file, the file path is the same for both.

I did do some changes to the completed file on which worksheets are being printed but that should not effect the macro, only which ones it will print/not print.

I'll try and duplicate the changes to the test file to see if it has any affects on the worksheet.

I have attached a couple images of the cells I changed.

Thanks,
Jonathan
change on sheets.jpg

summary sheet.jpg

**Keebellah** · 07-01-2019, 03:31 PM

Troubleshooting is the most time consuming part of coding.
Just run the macro line by line.
Open the VBA editor and select the printall macro, then Press F8 to start the macro and just press F8 for each one of code until it hits the error
The images you attach don't tell me anything, maybe check if the range / cell you query for the N value is not a merged cell.

**jkhuskies** · 07-01-2019, 04:00 PM

I was able to fix it. Regardless of a "Y" or "N" it should have been printing something.
What I had changed was a formula before not code so it makes no sense.
I changed another formula in the summary worksheet and it started working again.

I've gone through the whole line by line in the past but none that gave this much of a headache.

Thanks for the help!

**Keebellah** · 07-01-2019, 04:15 PM

I didn't do much, you did it all

Print to individual PDF files from list of Yes/No

Re: Print to individual PDF files from list of Yes/No

Re: Print to individual PDF files from list of Yes/No

Re: Print to individual PDF files from list of Yes/No

Re: Print to individual PDF files from list of Yes/No

Re: Print to individual PDF files from list of Yes/No

Re: Print to individual PDF files from list of Yes/No

Re: Print to individual PDF files from list of Yes/No

Re: Print to individual PDF files from list of Yes/No

Re: Print to individual PDF files from list of Yes/No

Re: Print to individual PDF files from list of Yes/No

Re: Print to individual PDF files from list of Yes/No

Re: Print to individual PDF files from list of Yes/No

Re: Print to individual PDF files from list of Yes/No

Re: Print to individual PDF files from list of Yes/No

Re: Print to individual PDF files from list of Yes/No

Re: Print to individual PDF files from list of Yes/No

Re: Print to individual PDF files from list of Yes/No

Re: Print to individual PDF files from list of Yes/No

Re: Print to individual PDF files from list of Yes/No

Re: Print to individual PDF files from list of Yes/No

Re: Print to individual PDF files from list of Yes/No

Re: Print to individual PDF files from list of Yes/No

Re: Print to individual PDF files from list of Yes/No

Re: Print to individual PDF files from list of Yes/No

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] how do I print .doc files from a list of pathnames in a spreadshee

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions