Macro to save multiple sheets to multiple PDF with cell value as file name

[thekatsmiaow]

Hi there, I've searched the forum but can't find exactly what I need.

• I would like a macro to be able to save 26 tabs within the one document to individual PDFs.
• Preferably I would like to be able to specify each time exactly which tabs get printed, because often I don't need to print all 26, just the first 10 or so.
• I would like each PDF to automatically be named with the value in cell E10 of each tab.
• E10 already has a formula to create its final value. It references cells from other tabs within the same document. Hopefully the fact that this cell has a formula in it won't affect my ability to use the resulting value as a 'save as' reference?
• I would like it if the PDFs save to the same location as the Excel sheet from which they're generated is located. The location of the excel sheet will change every three months, so I'd prefer not to specify a location with a specific filepath, as it will have changed by the time I run the macro again.

I have never used macros before, so an explanation or a link to a tutorial which explains where to paste the code would be most appreciated. And perhaps a button to press would help? I am using Excel 2010.

Thanks in advance to everyone who can help me.

 

[thekatsmiaow]

Any ideas from anyone on this?

 

[mick0005]

For what it is worth, I could also use this exact macro. I would change the cell from E10, but I would also use a formula to drive that cell.

thekatsmiaow: In the meantime while you are waiting for someone to save the day with some VBA, here will be how you'll put the code into excel:

1. Press Alt-F11 to open visual basic editor
2. Click Insert on the menu bar and choose "Module"
3. Paste the VBA code you receive from one of the heroes here
4. Exit visual basic editor

In order to make a button, follow the instructions at this link below (note, I am not affiliated with this blog at all, I just have this saved in my evernote archive and I find it to be a good tutorial):
http://blog.contextures.com/archives/2009/07/08/run-an-excel-macro-with-a-worksheet-button/

 

[thekatsmiaow]

Hi Mick, thanks for your reply! That's a big help already, and hopefully someone will chime in with some code to help us.