Solved Macro Code, save as PDF

January 19, 2012 at 11:14:46
Specs: Windows 7
Excel Macros
I am trying to create a macro for Excel 2010 that will save each Excel worksheet, within a workbook, to be save as a PDF and the file name be the tab name.
Does anyone know if this is possible? Thanks

See More: Macro Code, save as PDF

Report •


✔ Best Answer
January 19, 2012 at 21:17:14
This seemed to work.

Some notes:

- You'll have to change the path to match where you want the PDF's saved.

- You can change OpenAfterPublish:=True to False if you don't want to see each PDF after it's created.

- The code will overwrite a file with the same name without asking.

You have been warned! ;-)

Sub CreatePDF()
 For sh = 1 To Sheets.Count
  Sheets(sh).ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Documents and Settings\username\Desktop\" & Sheets(sh).Name & ".pdf", _
          Quality:=xlQualityStandard, IncludeDocProperties:=True, _
          IgnorePrintAreas:=False, OpenAfterPublish:=False
 Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
January 19, 2012 at 13:31:55
How would you save an Excel file as PDF?

The only way I can do that is to use one of the free programs available that allows you to "print" your file to a PDF by choosing the PDF app as your printer.

CutePDF, PDFCamp Printer, etc.

If that is also how you would do it, then I might be able to come up with something that would work. If not, you'll need to supply your regular method of creating PDF's.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
January 19, 2012 at 13:42:35
I have Excel 2010 and I go File>Save As> and change the "Save as type" to PDF (*.pdf) . I also have acrobat on my computer so I have the Acrobat Tab that has "create PDF" as an option.

I am going to have a workbook that is going to have at least 80 tabs to it and don't want to do that for all of them.


Report •

#3
January 19, 2012 at 13:58:00
Would it be possible for you to record a macro as you save 2 or 3 tabs as PDF files and then post the code here?

I might be able to modify it so that it loops through the sheets.

Please click on the blue line at the end of this post and read the instructions on how to post example data in this forum.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
January 19, 2012 at 14:04:20
Yeah, I'm not 100% sure how to do that :). Help?

Report •

#5
January 19, 2012 at 19:30:20
DerbyDad03, FYI 2007 has a Save As PDF option.

MIKE

http://www.skeptic.com/


Report •

#6
January 19, 2012 at 21:17:14
✔ Best Answer
This seemed to work.

Some notes:

- You'll have to change the path to match where you want the PDF's saved.

- You can change OpenAfterPublish:=True to False if you don't want to see each PDF after it's created.

- The code will overwrite a file with the same name without asking.

You have been warned! ;-)

Sub CreatePDF()
 For sh = 1 To Sheets.Count
  Sheets(sh).ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Documents and Settings\username\Desktop\" & Sheets(sh).Name & ".pdf", _
          Quality:=xlQualityStandard, IncludeDocProperties:=True, _
          IgnorePrintAreas:=False, OpenAfterPublish:=False
 Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
January 19, 2012 at 21:18:22
Thanks Mike.

I wasn't sitting at a machine with 2007/2010 at the time so I couldn't test anything.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
January 20, 2012 at 05:57:47
IT WORKS PERFECTLY!!! Thank you so much. You just saved me a LOT of time with an 80 worksheet, workbook.

Thanks again.


Report •

Ask Question