Selecting multiple sheets with SaveAs type PDF
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Selecting multiple sheets with SaveAs type PDF
Can the SaveAs type PDF select all visible sheets and not just the ActiveSheet
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Selecting multiple sheets with SaveAs type PDF
In the Save As dialog, after selecting PDF as file type, ciick Options:
In the Options dialog, select 'Entire workbook':
or select all sheets that you want to save beforehand, and select 'Active sheet(s)':
Click OK, then proceed with saving as usual.
In the Options dialog, select 'Entire workbook':
or select all sheets that you want to save beforehand, and select 'Active sheet(s)':
Click OK, then proceed with saving as usual.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Selecting multiple sheets with SaveAs type PDF
Very interesting, can this be accomplished thru VBA or is it a manual thing.
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Selecting multiple sheets with SaveAs type PDF
It can be done through VBA. If you have selected multiple sheets (I assume that you know how to do that in VBA), ActiveSheet.ExportAsFixedFormat will save all selected sheets. For example:
Code: Select all
Dim strFileName As String
strFileName = "..."
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=strFilename, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Selecting multiple sheets with SaveAs type PDF
I like this it saves in a manageable size pdf file even with a large amount of pictures. I’ll do some tweaking with this. Is these feature and code consistent with 2007, 2010 and 2013
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Selecting multiple sheets with SaveAs type PDF
It works in Excel 2010, and 2013 is very similar in this respect. I cannot test in 2007.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Selecting multiple sheets with SaveAs type PDF
So far it seems identical. I recorded a macro and it was just like the code I received from you and on a trial run it seems to work great. I just need to figure how to place finished pdf on desktop
I know its something similar to this
I know its something similar to this
Code: Select all
Dim WSHShell As Object
Dim DesktopPath As String
Set WSHShell = CreateObject("WScript.Shell")
DesktopPath = WSHShell.SpecialFolders("Desktop")
Set WSHShell = Nothing
ThisWorkbook.SaveAs Filename:=DesktopPath & "\" & _
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Selecting multiple sheets with SaveAs type PDF
You can combine it like this:
Code: Select all
Dim WSHShell As Object
Dim DesktopPath As String
Dim strFileName As String
Set WSHShell = CreateObject("WScript.Shell")
DesktopPath = WSHShell.SpecialFolders("Desktop")
Set WSHShell = Nothing
strFileName = DesktopPath & "\" & "myFile.pdf" ' change as needed
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=strFilename, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Selecting multiple sheets with SaveAs type PDF
Thank you Hans; this may be a better approach than trying to compress pictures, I hope so, it seems to have great promise (famous last words).
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Selecting multiple sheets with SaveAs type PDF
Keep in mind that the PDF file is merely a 'printout' of the workbook - it doesn't contain any formulas or macros. If that suits your purpose, that is fine of course.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Selecting multiple sheets with SaveAs type PDF
In my line of business a PDF file (report) is the only way to go. So far I’m liken this a lot