VBA save versions to same PDF

K

kcmuppet

Active Member
Joined
Nov 2, 2005
Messages
437
Office Version
  1. 365
Platform
  1. Windows
Hi,

I've got a 2-page report that I want to be able to save multiple versions of, in the same PDF. By 'versions' I mean with changed values based on user selection from a drop-down list (in cell B4)

I've got the code below to generate a PDF, but what I'd really like to have happen is that the user can save - in a single PDF - all the versions of the report, as if she had selected them one-by one from the drop-down list, and then run the macro for each and saved them one at a time. The drop-down list is an array of variable length defined as "VersionList"

Code: 
[COLOR=#E56717][FONT=Consolas][B]Sub[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] SaveThisOneAsPDF()[/FONT][/COLOR]
[COLOR=#8D38C9][FONT=Consolas][B]With[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] Application.FileDialog(msoFileDialogFolderPicker) [/FONT][/COLOR][COLOR=#008000][FONT=Consolas]' Select folder only - user won't have control over filename
[/FONT][/COLOR][COLOR=#141414][FONT=Consolas]        .Title = [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"Select where to save"[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        .ButtonName = [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"Use this folder"[/FONT][/COLOR][COLOR=#008000][FONT=Consolas]' instead of 'OK' button
[/FONT][/COLOR][COLOR=#141414][FONT=Consolas]        .AllowMultiSelect = [/FONT][/COLOR][COLOR=#00C2FF][FONT=Consolas][B]False[/B][/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        .Show[/FONT][/COLOR]
[COLOR=#8D38C9][FONT=Consolas][B]If[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] .SelectedItems.Count <> 0 [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]Then[/B][/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]            Sheet1.Range([/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"i6"[/FONT][/COLOR][COLOR=#141414][FONT=Consolas]) = .SelectedItems(1)[/FONT][/COLOR]
[COLOR=#8D38C9][FONT=Consolas][B]Else[/B][/FONT][/COLOR][COLOR=#008000][FONT=Consolas]'If user cancels
[/FONT][/COLOR][COLOR=#141414][FONT=Consolas]           MsgBox [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"Not Saved"[/FONT][/COLOR][COLOR=#141414][FONT=Consolas], vbExclamation, [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"FYI:"[/FONT][/COLOR]
[COLOR=#E56717][FONT=Consolas][B]Exit[/B][/FONT][/COLOR][COLOR=#E56717][FONT=Consolas][B]Sub[/B][/FONT][/COLOR]
[COLOR=#8D38C9][FONT=Consolas][B]End[/B][/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]If[/B][/FONT][/COLOR]
[COLOR=#8D38C9][FONT=Consolas][B]End[/B][/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]With[/B][/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]    ActiveSheet.ExportAsFixedFormat _[/FONT][/COLOR]
[COLOR=#151B8D][FONT=Consolas][B]Type[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas]:=xlTypePDF, _[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        Filename:=Range([/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"I7"[/FONT][/COLOR][COLOR=#141414][FONT=Consolas]), _[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        Quality:=xlQualityStandard, _[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        IncludeDocProperties:=[/FONT][/COLOR][COLOR=#00C2FF][FONT=Consolas][B]True[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas], _[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        IgnorePrintAreas:=[/FONT][/COLOR][COLOR=#00C2FF][FONT=Consolas][B]False[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas], _[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        OpenAfterPublish:=[/FONT][/COLOR][COLOR=#00C2FF][FONT=Consolas][B]True[/B][/FONT][/COLOR]

[COLOR=#8D38C9][FONT=Consolas][B]End[/B][/FONT][/COLOR][COLOR=#E56717][FONT=Consolas][B]Sub[/B][/FONT][/COLOR]

[COLOR=#E56717][FONT=Consolas][B]Sub[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] SaveAlltoPDF()[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]MsgBox ([/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"Still working out how to do it!"[/FONT][/COLOR][COLOR=#141414][FONT=Consolas])[/FONT][/COLOR]
[COLOR=#8D38C9][FONT=Consolas][B]End[/B][/FONT][/COLOR][COLOR=#E56717][FONT=Consolas][B]Sub[/B][/FONT][/COLOR]

Your help/tips/advice greatly appreciated.
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Sort by date Sort by votes
I've moved it on a bit with this modification, which loops through list and saves a PDF for each one.


Does anyone know of a way to make the macro put all the versions combined into a single PDF?



Code: 
[COLOR=#E56717][FONT=Consolas][B]Sub[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] SaveAlltoPDF()[/FONT][/COLOR]
[COLOR=#151B8D][FONT=Consolas][B]Dim[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] itm [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] Range, listrng [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] Range, selecteditm [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] Range[/FONT][/COLOR]

[COLOR=#8D38C9][FONT=Consolas][B]With[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] Application.FileDialog(msoFileDialogFolderPicker) [/FONT][/COLOR][COLOR=#008000][FONT=Consolas]' Select folder only - user won't have control over filename
[/FONT][/COLOR][COLOR=#141414][FONT=Consolas]        .Title = [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"Select where to save"[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        .ButtonName = [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"Use this folder"[/FONT][/COLOR][COLOR=#008000][FONT=Consolas]' instead of 'OK' button
[/FONT][/COLOR][COLOR=#141414][FONT=Consolas]        .AllowMultiSelect = [/FONT][/COLOR][COLOR=#00C2FF][FONT=Consolas][B]False[/B][/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        .Show[/FONT][/COLOR]
[COLOR=#8D38C9][FONT=Consolas][B]If[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] .SelectedItems.Count <> 0 [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]Then[/B][/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]            Sheet1.Range([/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"i6"[/FONT][/COLOR][COLOR=#141414][FONT=Consolas]) = .SelectedItems(1)[/FONT][/COLOR]
[COLOR=#8D38C9][FONT=Consolas][B]Else[/B][/FONT][/COLOR][COLOR=#008000][FONT=Consolas]'If user cancels
[/FONT][/COLOR][COLOR=#141414][FONT=Consolas]MsgBox [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"Not Saved"[/FONT][/COLOR][COLOR=#141414][FONT=Consolas], vbExclamation, [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"FYI:"[/FONT][/COLOR][COLOR=#141414][FONT=Consolas]
[/FONT][/COLOR][COLOR=#E56717][FONT=Consolas][B]Exit[/B][/FONT][/COLOR][COLOR=#E56717][FONT=Consolas][B]Sub[/B][/FONT][/COLOR]
[COLOR=#8D38C9][FONT=Consolas][B]End[/B][/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]If[/B][/FONT][/COLOR]
[COLOR=#8D38C9][FONT=Consolas][B]End[/B][/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]With[/B][/FONT][/COLOR]


[COLOR=#151B8D][FONT=Consolas][B]Set[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] listrng = Sheet2.Range([/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"dd_List"[/FONT][/COLOR][COLOR=#141414][FONT=Consolas])[/FONT][/COLOR]
[COLOR=#151B8D][FONT=Consolas][B]Set[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] selecteditm = Sheet1.Range([/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"B4"[/FONT][/COLOR][COLOR=#141414][FONT=Consolas])[/FONT][/COLOR]

[COLOR=#8D38C9][FONT=Consolas][B]For[/B][/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]Each[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] itm [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]In[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] listrng[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]    selecteditm = itm[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]      ActiveSheet.ExportAsFixedFormat _[/FONT][/COLOR]
[COLOR=#151B8D][FONT=Consolas][B]Type[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas]:=xlTypePDF, _[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        Filename:=Range([/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"I7"[/FONT][/COLOR][COLOR=#141414][FONT=Consolas]), _[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        Quality:=xlQualityStandard, _[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        IncludeDocProperties:=[/FONT][/COLOR][COLOR=#00C2FF][FONT=Consolas][B]True[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas], _[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        IgnorePrintAreas:=[/FONT][/COLOR][COLOR=#00C2FF][FONT=Consolas][B]False[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas], _[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        OpenAfterPublish:=[/FONT][/COLOR][COLOR=#00C2FF][FONT=Consolas][B]False[/B][/FONT][/COLOR]
[COLOR=#8D38C9][FONT=Consolas][B]Next[/B][/FONT][/COLOR]

[COLOR=#8D38C9][FONT=Consolas][B]End[/B][/FONT][/COLOR][COLOR=#E56717][FONT=Consolas][B]Sub[/B][/FONT][/COLOR]
 
Upvote 0
kcmuppet said:
Does anyone know of a way to make the macro put all the versions combined into a single PDF?
Click to expand...
Add a new temporary workbook and copy each sheet 'version' to that workbook, then save the entire workbook as a PDF file. You should be able to get the starting code for these steps via the macro recorder.
 
Upvote 0
You must log in or register to reply here.

Similar threads

tlc53
  • Question
VBA Save All Relevant Report into One Document
Replies
5
Views
439
tlc53
tlc53
G
  • Question
PDF Create button to manage output formatting
Replies
0
Views
717
grabrail
G
JoshLyman
  • Solved
Save specific sheet as PDF
2
Replies
11
Views
2K
tahmed5835
T
S
  • Solved
PDFing two tabs in one workbook in different formats
Replies
2
Views
344
stroffso
S
D
  • Solved
VBA save as PDF overwrite warning code
Replies
4
Views
709
drefiek2
D

Forum statistics

Threads
1,214,979
Messages
6,122,550
Members
449,088
Latest member
davidcom

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top