VBA code to print changing number of sheets to separate pdf files and save with specific names

BeeJay · Mar 19, 2014

I have no clue about VBA, except for any macro's that I recorded myself in excel.

What I'm looking for is the following:

I would like to be able to select a certain number of sheets in an excel file. Which sheet I would like to decide on the spot so to say.
Each selected sheet should then be stored as a separate PDF file in the same folder as where the xlsx file is stored in.

The PDF files should be saved as follows:
"Sheetname" + "specific text".

The specific text should be asked for somewhere when the macro is being executed. And this specific text is the same for each individual pdf.

I'm working with excel 2010.

Could anybody assist?

sijpie · Mar 20, 2014

I recently made something very similar for another thread on this board, so I have adjusted that for you: (original thread http://www.mrexcel.com/forum/excel-...book-hundreds-sheets-list-specific-sheet.html)

you will need to do some work here. It is not difficult if you follow the instructions exactly. What we are going to do is create a userform where you can select the sheet names and enter a basename for the pdf file names. The form will handle what to do when you press the OK button.

Open your workbook (with all the sheets). Close any other workbooks to keep things easy.
Now press Alt-F11 to open the <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help;">VBA</acronym> editor. You will see in the top left panel your workbook with all the sheets listed.
Somewhere there right click your mouse and select 'Insert...' / 'User Form'
In the right panel an empty userform appears.
Hopefully in the bottom left panel a list of properties for the form is shown. If not press F4.
The very first item of the properties is '(Name)' which reads UserForm1
This is pretty meaningless, so rename it to 'SheetsForPrinting'
Go down the list in the properties to 'Caption'. It says 'UserForm1'.
Change the caption to "Sheets for Printing"
Look at your form, you can see the new title it has.
Click once on the form. Hopefully a little toolbox window will appear. if not goto 'View' in the menu and select 'Toolbox'
You want to know what the form does, so let's put some text on it. click on the 'A' in the toolbox to paint a label.
Take your mouse to the userform and from top left to about a cm down and to the right of the form 'paint' a label box
put the cursor in this box and type (or copy /paste from here):"Select the sheets you want to have printed form the list. Use Ctrl key to select multiple individual sheets"
OK, now we need to be able to do something useful with the form. Go to the Toolbox and hover your mouse over the icons till you find 'ListBox' (normally 1st icon on second row). Click on it
Now back to the userform and paint a square below your label to just above the bottom of the form. This is where the sheet names will appear (when called)
We also want to be able to enter a base name for the pdf output (they will all get a sequential number and date appended)
So from the toolbox select the ab| icon and paint a textbox a bit below the list box (see image below to get an idea. You can make the form bigger by dragging the bottom edge)
We also want a label above it to explain what the textbox is for: make a label and give it the caption: 'Base Name for PDF output'
Lastly we need some buttons: on the Toolbox find the little button icon (CommandButton). Click it
Back to the form and to the right of our square paint a button
click once on the button. Then hold down the Ctrl key and drag a copy of the button to below the first button (handy way to keep buttons same size)
In the Properties window (bottom right) change the caption of the button to 'Cancel'
Click once on the first button
In the Properties window change the caption of the button to 'OK'
Go a bit further down the properties list to 'Default'. set that to True (this means it will react to the Enter key)

Hey you now have a wonderful form! Should look something like this:

ZJT6Oxkvuw2_Mnx1mUQtE9yiBHQav4YDlfOnGuhzh3w=w241-h232-p-no

Click on your userform title and press F5. Hey there it is. But the list is empty and the buttons don't do anything.
Press the red close button to close the form. Let's go add some functionality...
Now let's start with the cancel button. Double click on the cancel button. The form disappears and an empty white space is looking at you with a skeleton macro in it for the cancel button.
Delete everything and paste the following text:

Option Explicit Private Sub CommandButton2_Click() ' Cancel Button Unload Me End Sub

Basically it says: if you click the cancel button then unload (close) the userform (Me)
You can try it. Press F5. the cancel button should now work
Now paste the following code below the code of the cancel button

Private Sub CommandButton1_Click() ' OK button Dim i As Long, j As Long Dim vShList As Variant Dim sFName As String With ListBox1 ' use an array to store the selected sheets ReDim vShList(1 To .ListCount, 1 To 1) j = 1 For i = 0 To .ListCount - 1 If .Selected(i) Then vShList(j, 1) = .List(i) j = j + 1 End If Next i If j = 1 Or TextBox1.Value = vbNullString Then 'no sheet was selected or no name was given MsgBox "At least one sheets needs to be selected and an output name to be entered", vbCritical Else ' we have sheets selected and a valid base name j = 1 ' filename counter sFName = ThisWorkbook.Path & "\" & TextBox1.Value For i = 1 To UBound(vShList, 1) If Not IsEmpty(vShList(i, 1)) Then 'print the sheet PrintAsPDF CStr(vShList(i, 1)), sFName & " - " & Format(j, "000") & " - " & Format(Date, "yy-mm-dd") j = j + 1 End If Next i End If Unload Me End With End Sub Private Sub UserForm_Initialize() Dim wsWS As Worksheet ' fill the list box with all sheet names, but not Main_List For Each wsWS In ThisWorkbook.Worksheets If wsWS.Name <> "Main_List" Then ListBox1.AddItem (wsWS.Name) End If Next wsWS ' put a default name for the base PDF name TextBox1.Value = Left(ThisWorkbook.Name, InStr(1, ThisWorkbook.Name, ".") - 1) End Sub 

OK now we have added the code for the OK button and for loading (starting) the userform.
Press F5 again. Wow! you can see all the sheets in the listbox. Select a few. Now press the OK button. Gosh you get an error message. That is because it expects a print sup which we haven't created yet. End the macro.

OK so we need two more things. We need to call up the user form. So we write a little macro and attach that to a button which we stick in Main_List. We also need a little pdf print macro. This is called by the userform.

Go back to the <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help;">VBA</acronym> editor. Now right click again on the workbook in the top left panel and select 'Insert..' / 'Module'
A new section Modules will appear below the workbook and the right panel has cleared with the new module open.
Here we can write macros that we can access from the workbook.
Paste the following macro:

Option Explicit Sub SelectSheets() ' call userform to select sheets for printing SheetsforPrinting.Show End Sub Sub PrintAsPDF(ByVal sWS2Print As String, ByVal sSaveName As String) ' Print Macro ' 'PDF export, do not open directly in Acrobat _ in case there are many hundreds printed Sheets(sWS2Print).ExportAsFixedFormat _ Type:=xlTypePDF, _ Filename:=sSaveName, _ OpenAfterPublish:=False End Sub

Now if you want to run this using a shortcut key, go to Excel. Press Alt-F8. It will show a dialog box with your macro 'SelectSheets' shown.
Select the macro (don't double click)
Press the Option... button and select a suitable shortcut sheet for it (preferably something with Shift, as a lot of the other keys already do something)
Click OK and then go back to the sheet. In cell D1 or so type your shortcut in (Ctrl-Shift-S for instance) so you don't forget

Then try it.
Finished

If you rather have a button, then you probably first need to enable the 'Developers' menu.
Right click on the menu ribbon, select 'Customise the Ribbon...'
In the right panel check the tickbox infront of 'Developer'.Click OK
Go to the Developer menu
Press the 'Insert' icon
select the button from the 'Form Controls' (top left icon)
in your spreadsheet paint your button
A dialog box opens to ask which macro it should run. Select your macro, click OK
The button appears, Click on the button to edit the text to somethin meaning full (right clicking allows you to change size, position, text)

Press the button

Let me know if this worked and how happy you now are!

I haven't added any checking to see if the filenames already exist, so if you run it several times on a day, you need to delete or move the pdfs first. Also I have made it so that the pdfs are stored in the same folder as the workbook running the macro.

BeeJay · Mar 24, 2014

Thanks for your reply.It looks fantastic, but I need some time to work this out. Will let you know soonest if it works.

Many thanks anyway for the time you spend to answer my question.

Hartelijk dank!

BeeJay · Mar 24, 2014

Oké, it didn't take too much time after all. Any way still have a couple of loose ends on this:

1. the CTRL function does not work, so I can select only one sheet at the time
2. I would like to use this function in various excel files
3. When the files are saved, this should happen as follows (if possible):
Sheetname1, "inputted text".pdf
Sheetname2, "inputted text".pdf
Sheetname3, "inputted text".pdf
The filename, number and date etc. are not necessary when the pdf's are saved.

Pleased to hear!

sijpie · Mar 24, 2014

OK, easy fixes. I see I forgot to set a property for the list box.
Open the VBA editor, and go into userform code. Here we will first change the setting for the pdf file names.

Locate these lines:

Code:

                    'print the sheet
                    PrintAsPDF CStr(vShList(i, 1)), sFName & " - " & Format(j, "000") & " - " & Format(Date, "yy-mm-dd")

Now change the second line to:

Code:

                    PrintAsPDF CStr(vShList(i, 1)), sFName & " - inputted text" )

OK now go to the userform itself.
click once on the listbox (the larger square) and then check in the properties list (lower left) for MultiSelect and set the value to: 2-fmMultiSelectExtended

Great, test the code and hopefully you will be able to select more sheets (using the ctrl key) and the names will come out OK.

Once you are happy you can think about getting this to work on other excel sheets. The finest solution would be to create an Add-In which would put this into the menu, etc. but let's not run before we can walk.

The second best thing would be to create a personal workbook and copy this macromodule and userform in it. You would open up the personal workbook when you start Excel and then when you need it you call up the macro (using Alt-F8).

The last option, if it is only a few workbooks that need this, is to copy the userform and macro module to each of them.

For option 2 and 3 you can do the same thing.

Have this workbook and the other workbook open at the same time.
Now go into the VBA editor. In the list top left you will see both (or more) books.
Now you can drag the userform from this workbook to any other in the list. Do the same with the macro module. That's it! job done. Now the userform is available int the other workbook as well.

BeeJay · Mar 25, 2014

Thanks for your assistance which is most appreciated.

The Multiselect part works great.

For this part: PrintAsPDF CStr(vShList(i, 1)), sFName & " - inputted text" ) I receive a compiler error. And I fear we have a misunderstanding on this part. The Inputted text should off course be the value of the TextBox1.Value.
so each separate pdf file should be saved with the name Sheet + the content of what is in the Text Box.

And I do think I would need an add-in in the long run because there also other users which would need this and the solutions as described above may be over their head.

sijpie · Mar 25, 2014

Ah, of course. I was sleeping...

In the code for the button click find the 'Else' statement
replace the text between 'Else' and 'End If' with

Code:

            ' we have sheets selected and a valid base name
            j = 1   ' filename counter
            sFName = ThisWorkbook.Path & "\"
            
            For i = 1 To UBound(vShList, 1)
                If Not IsEmpty(vShList(i, 1)) Then
                    'print the sheet
                    PrintAsPDF CStr(vShList(i, 1)), sFName & vShList(i, 1) & TextBox1.Value
                    j = j + 1
                End If
            Next i

I'll think about the add-in (busy on another project at the moment)

BeeJay · Mar 25, 2014

Code works wonderfull now! Fantastic.

If you have some spare time in near future to have this incorporated in an add-in that would be great!

sijpie · Mar 26, 2014

OK, I have made an Add-in, and I will post it so you can download it ready-made. But as this board is also used for learning, I am adding all the steps here to get to the Add-in and how to install it.
Note that the add-in will add a menu item, but is using Excel 1997-2000 code to do so. This means that the menu can be found in Excel 2007+ under an Add-In ribbon tab. I have not given it a real 2010+ Ribbon of its own.

If you want to create the Add-in yourself, then basically you start with an empty new workbook. You can save it as a normal workbook first, later we will save it as an Add-in workbook.

You more or less need to follow the steps above to create the userform and additional code. As I have modified the userform to add some more functionality, here is a screenshot of what my userform looks like in the VBE (VBA Editor)

voCCTES--u0cbv5QKGD_U_3DZJPFTlTy-sm3KYJ_IgU=w185-h228-p-no

In addition to the earlier userform I have a few more lables, one of which will show the user an example of what the file names will look like with the data he enters in the Textbox.
Another addition is a checkbox, which when left checked will open Windows Explorer in the folder so the user can see the pdf files.

So create the userform.
The label with 'Example' I have renamed lblExample to make it clear in the code what it is for. I have set the properties to Blue text, so the user will see it better.

The main change in the code (other than the added functionality) is that we can't use 'ThisWorkbook' as that would refer to the Add-in which is more or less invisible to the user, and is not the one on which the actions should take place. So we use 'ActiveWorkbook' instead, which refers to the workbook from which the user will be calling the macro.

So the code behind the Userform now is as follows:

Option Explicit Private Sub btnCancel_Click() bCancel = True ' flag to avoid opening Windows Explorer Unload Me End Sub Private Sub btnPrint_Click() ' Print button Dim i As Long, j As Long Dim vShList As Variant Dim sFName As String With ListBox1 ' use an array to store the selected sheets ReDim vShList(1 To .ListCount, 1 To 1) j = 1 For i = 0 To .ListCount - 1 If .Selected(i) Then vShList(j, 1) = .List(i) j = j + 1 End If Next i If j = 1 Or TextBox1.Value = vbNullString Then 'no sheet was selected or no name was given MsgBox "At least one sheets needs to be selected and an output name to be entered", vbCritical Else ' we have sheets selected and a valid base name j = 1 ' filename counter sFName = ActiveWorkbook.Path & "\" For i = 1 To UBound(vShList, 1) If Not IsEmpty(vShList(i, 1)) Then 'print the sheet PrintAsPDF CStr(vShList(i, 1)), sFName & vShList(i, 1) & " - " & TextBox1.Value j = j + 1 End If Next i End If Unload Me End With End Sub Private Sub CheckBox1_Click() ' cancel opening Windows Explorer after printing bCancel = Not CheckBox1.Value End Sub Private Sub TextBox1_Change() ' update the Example file name lblExample.Caption = ListBox1.List(1) If Len(TextBox1) > 20 Then lblExample.Caption = lblExample.Caption & " - " & Left(TextBox1.Value, 10) & "..." & Right(TextBox1.Value, 10) Else lblExample.Caption = lblExample.Caption & " - " & TextBox1.Value End If End Sub Private Sub UserForm_Activate() Dim wsWS As Worksheet ' fill the list box with all sheet names, but not Main_List For Each wsWS In ActiveWorkbook.Worksheets If wsWS.Visible = xlSheetVisible Then ' Hidden sheets should not be included: _ 1. they won't print (error) _ 2. there is a reason the are hidden ListBox1.AddItem (wsWS.Name) End If Next wsWS ' put a default name for the base PDF name TextBox1.Value = Left(ActiveWorkbook.Name, InStr(1, ActiveWorkbook.Name, ".") - 1) End Sub 

Then we have the code which calls the userform, ie the code that runs when the user clicks on the menu item (or calls it from the macro dialog). The code is sitting in a normal module.

This code now looks as follows:

Option Explicit Public bCancel As Boolean ' flag yes/no opening Windows Explorer Sub SelectSheets4Print() ' call userform to select sheets for printing SheetsForPrinting.Show If Not bCancel Then ' open Windows Explorer in the directory Shell "explorer.exe " & ActiveWorkbook.Path, vbNormalFocus Else MsgBox "Finished printing to pdf" End If End Sub Sub PrintAsPDF(ByVal sWS2Print As String, ByVal sSaveName As String) ' Print Macro ' 'PDF export, do not open directly in Acrobat _ in case there are many hundreds printed On Error GoTo Err ' Errors are thrown if sheets are hidden or nothing to print _ (Hidden sheets should not show up in list) ActiveWorkbook.Sheets(sWS2Print).ExportAsFixedFormat _ Type:=xlTypePDF, _ Filename:=sSaveName, _ OpenAfterPublish:=False On Error GoTo 0 Exit Sub Err: MsgBox "Error printing sheet " & sWS2Print On Error GoTo 0 End Sub 

Lastly we need to put in the Add-in required code. What needs to happen is that when the workbook (an add-in is still a workbook) is opened by Excel (for an add-in that is when Excel starts up), it installs our menu item. And when it exits (upon closing excel or unloading the add-in by the user) the menu item disappears. In case Excel crashed, we don't want to install a second identical menu item, so we delete any existing one first.

To get this done we need to write the code to do this into the Workbook module.
For this you need to in the VBE double-click on 'WorkBook' under your workbook name. This will open its module. Here we can put code that runs on opening and on closing the workbook.

SP1hk1qjDxDmXPT_k8TbjIhYql_p-9Wor7ikYE--XAY=w482-h238-p-no

OK. So paste the following code in here:

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' START ThisWorkbook Code Module ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Option Explicit Private Const C_TAG = "McSijpP2PDFAddIn" ' C_TAG should be a string unique to this add-in. Private Const C_TOOLS_MENU_ID As Long = 30007& Private Sub Workbook_Open() ''''''''''''''''''''''''''''''''''''''''''''''' ' Workbook_Open ' Create a submenu on the Tools menu. The ' submenu has two controls on it. ''''''''''''''''''''''''''''''''''''''''''''''' Dim ToolsMenu As Office.CommandBarControl Dim ToolsMenuItem As Office.CommandBarControl Dim ToolsMenuControl As Office.CommandBarControl ' First delete any of our controls that ' may not have been properly deleted previously. DeleteControls ' Get a reference to the Tools menu. Set ToolsMenu = Application.CommandBars.FindControl(ID:=C_TOOLS_MENU_ID) If ToolsMenu Is Nothing Then MsgBox "Unable to access Tools menu.", vbOKOnly Exit Sub End If ' Create a item on the Tools menu. Set ToolsMenuItem = ToolsMenu.Controls.Add(Type:=msoControlPopup, temporary:=True) If ToolsMenuItem Is Nothing Then MsgBox "Unable to add item to the Tools menu.", vbOKOnly Exit Sub End If With ToolsMenuItem .Caption = "&Print to PDF" .BeginGroup = False .OnAction = "'" & ThisWorkbook.Name & "'!SelectSheets4Print" .Tag = C_TAG End With ''''''''''''''''''''''''''''''''''''''''''''''' '' Create the first control on the new item '' in the Tools menu. ''''''''''''''''''''''''''''''''''''''''''''''' 'Set ToolsMenuControl = ToolsMenuItem.Controls.Add(Type:=msoControlButton, temporary:=True) 'If ToolsMenuControl Is Nothing Then ' MsgBox "Unable to add item to Tools menu item.", vbOKOnly ' Exit Sub 'End If ' 'With ToolsMenuControl ' '''''''''''''''''''''''''''''''''''' ' ' Set the display caption and the ' ' procedure to run when clicked. ' '''''''''''''''''''''''''''''''''''' ' .Caption = "&Click Me One" ' .OnAction = "'" & ThisWorkbook.Name & "'!MacroToRunOne" ' .Tag = C_TAG 'End With ' ''''''''''''''''''''''''''''''''''''''''''''''' '' Create the first control on the new item '' in the Tools menu. ''''''''''''''''''''''''''''''''''''''''''''''' 'Set ToolsMenuControl = ToolsMenuItem.Controls.Add(Type:=msoControlButton, temporary:=True) 'If ToolsMenuControl Is Nothing Then ' MsgBox "Unable to add item to Tools menu item.", vbOKOnly ' Exit Sub 'End If ' 'With ToolsMenuControl ' '''''''''''''''''''''''''''''''''''' ' ' Set the display caption and the ' ' procedure to run when clicked. ' '''''''''''''''''''''''''''''''''''' ' .Caption = "&Click Me Two" ' .OnAction = "'" & ThisWorkbook.Name & "'!MacroToRunTwo" ' .Tag = C_TAG 'End With End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) '''''''''''''''''''''''''''''''''''''''''''''''''''' ' Workbook_BeforeClose ' Before closing the add-in, clean up our controls. '''''''''''''''''''''''''''''''''''''''''''''''''''' DeleteControls End Sub Private Sub DeleteControls() '''''''''''''''''''''''''''''''''''' ' Delete controls whose Tag is ' equal to C_TAG. '''''''''''''''''''''''''''''''''''' Dim Ctrl As Office.CommandBarControl 'On Error Resume Next Set Ctrl = Application.CommandBars.FindControl(Tag:=C_TAG) Do Until Ctrl Is Nothing Ctrl.Delete Set Ctrl = Application.CommandBars.FindControl(Tag:=C_TAG) Loop End Sub ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' END ThisWorkbook Code Module ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' 

Save your workbook.

In the VBE, go to the workbook module (the one we just created), click once in the WorkBook_Open macro and press F5 key.
Go back to Excel, and check, you should have a Add-In tab in the menu and if you go there there should be a menu item we just created.

Open another workbook. Go to this menu item and click it. It should work and print your sheets if you ask.

So once it is tested OK, then go back to the Print2PDF workbook. We want to put some comments in the file Properties so we know what this add-in does:
Go to the File menu, Info. On the right panel you see the properties. Expand the list by clicking on 'Show All Properties'
Click on 'Add a Category' and type 'Utility'
Click on 'addComments' and type in 'This add-in makes it easy to print selected sheets to .pdf files'
Click on 'add Title' and give it a title as well.
Now save as Add-In (.xlam for 2007+, .xla if you still use an older version of Excel)

Excel will propose to save it in its Add-in directory, which probably is fine.

Now close Print2PDF.

Install the Add-in:
go to the File tab, Options, Add-Ins, and press the Go... button bottom middle. Now you see a list of installed (with tick) and not installed add-ins. Put a tick infront of the PrintSheets2PDF (you can now see your comments here) and OK.

The add-in will load and you now have the extra menu available everytime you start Excel.

wonderful or what?

So you don't want to go through all this work yourself? Well download the add-in here, put it in your add-in folder (or any other folder that won't move about) and go through the last steps to install it.

here is the link. The file is unprotected so you can check / modify the code if you want.
Printto PDF Add-in: https://drive.google.com/file/d/0BxykuY6uKAJ9LXZBUGxIQTJNV00/edit?usp=sharing

BeeJay · Mar 26, 2014

This is fantastic!

VBA code to print changing number of sheets to separate pdf files and save with specific names

BeeJay

New Member

Excel Facts

sijpie

Well-known Member

BeeJay

New Member

BeeJay

New Member

sijpie

Well-known Member

BeeJay

New Member

sijpie

Well-known Member

BeeJay

New Member

sijpie

Well-known Member

BeeJay

New Member

Similar threads

Forum statistics

Share this page

VBA code to print changing number of sheets to separate pdf files and save with specific names

New Member

Excel Facts

Well-known Member

New Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock