Entire Workbook to PDF and more

[pick-six]

Hello everyone,

1. I need a code that will convert an ENTIRE workbook into one PDF file

2. I would like it to save to the same location as excel file

3. I would like it to use the value of "J52" located on the "Information" worksheet as the name of the file

4. I would like to activate the macro using any keyboard shortcut...... possibly ctrl + shift + d

Is this possible?




5. I also use the code listed below to convert an individual worksheet into a pdf. I don't remember where I found it. It works ok now. However, when I use it, the save-as window with the correct file name displays and wants to save it to my documents unless you change it. I would like it do the same thing but the default location be the same as the original excel file. Can someone clean this up and make that work?

Thanks!

Please Login or Register  to view this content.

[Blokeman]

Hi, Welcome to the forum
Please use code tags. What is the directory of the original excel file?

[pick-six]

Hi, Welcome to the forum
Please use code tags. What is the directory of the original excel file?

The spreadsheet will be used by multiple people so the directory could vary. If the directory has to be defined then I would just rather it save to the desktop. I will try to go back and edit my first post with code tags.

Thanks

[pick-six]

This code posted by Paul in another thread works great if only it would use a cell value for the filename and had a keyboard shortcut. It saves it to the same directory as the original excel file.

Please Login or Register  to view this content.

[Blokeman]

try;

Please Login or Register  to view this content.

Read this for keyboard shortcut;https://support.office.com/en-ca/art...3-50e645fe3155

[pick-six]

Thanks Blokeman!!

That code worked for the entire workbook. It saves the pdf based on the cell value too. However, it now does not save it in the same directory as the original excel file. I'm gettting closer though!

[jolivanes]

For the first part, you could try this.

Please Login or Register  to view this content.

You might be able to read through this code to make changes for the single sheet code.
If not, let us know.

The shortcut you'll have to do manually
Developer - Macros - Options

[pick-six]

jolivnaes.....I couldn't get your code to work.

This code does work but it does not save to the current directory as the it did before I edited it with Blokemans code. It saves it to mydocuments instead.

Please Login or Register  to view this content.

[jolivanes]

I made a slight mistake in the previous code. The filename was from the wrong Sheet/Cell Address.
It works for me.

Please Login or Register  to view this content.

[pick-six]

jolivanes,

Does your code require that I specify the sheet names? I can't get it to work. This spreadsheet has about 15 worksheets. In some instances many of them would not be used so I will need a code that will only convert the worksheets that aren't hidden. When I try to use your code I get an error. I think it is on this line

Please Login or Register  to view this content.

[jolivanes]

It helps if all the information is supplied.

Please Login or Register  to view this content.

[pick-six]

Jolivanes your code works great and that solved my first problem! Thank you very much for your help. I'm still playing with the second part of my original post. I haven't had any luck getting the Individual worksheet to save in the same directory as the original excel file.

[jolivanes]

Is that going to be the Active, or visible/open, sheet or a sheet name somewhere in cell in a sheet ?

[pick-six]

Yes.... The active sheet is open and that sheet has a button that starts the macro. I use the same macro many times throughout the workbook but it is only used on the active sheet.

[jolivanes]

Is the name it will be saved as in Cell N75?

[jolivanes]

Change the cell reference for the file name as required.

Please Login or Register  to view this content.

Or with an InputBox

Please Login or Register  to view this content.

[pick-six]

Yes it will

[jolivanes]

Re: "Yes it will"
Change the cell reference in the first code in post #16 to the right cell address and you should be sailing into the sunset.

BTW, you might be interested in this article.
http://www.contextures.com/excel-qui...customize.html

[pick-six]

It works. Thanks for your help!

[jolivanes]

Glad we got it working.
Good luck
John

[pick-six]

I have been using this code that Jolivanes helped me with for a few months. Is there anyway I can use this same code and attach the pdf to an outlook email. I have a code that works for an individual worksheet but I need one that will convert all active sheets into a pdf then email. This is the code I use to convert the active sheets into a pdf.

Please Login or Register  to view this content.