Hello Excellers and welcome to another #Excel #MacroMonday blog post in my 2021 series. I want to show you how to export and save all of your Excel worksheets in an Excel workbook as separate PDF files. This is particularly useful if you have as in my example multiple sales regions and you need to save the worksheets for each region in their own file.
In my example, I have four sales regions. I need to save each region’s report separately and send it as a PDF file to the regional managers. So, instead of making four copies of the Excel workbook then deleting three regionas out of each of the workbooks, let;s write some grest VBA to do the job for us.
This macro will easily do the job for you with a couple of clicks. Good stuff!.
Storing The Excel Macro.
Before we begin to write any code you need to decide where to store the code. You have a choice.
- In the current Excel workbook.
- In your Personal Macro Workbook.
What’s the difference?. Well, if you save the macro in your Personal Macro workbook it will be available in any Excel workbooks. If you store it in the current workbook then use is restricted to that workbook. In this instance, I want to use this macro specifically in this workbook over and over again so I will save it in the actual workbook.
If you want to read more about the Personal Macro Workbook feel free to read my blog posts below.
Creating and Updating Your Personal Macro Workbook
Create A Shortcut To Your Personal Excel Macro Workbook
Why Is My Personal Macro Workbook Not Loading Automatically?
Preparing To Write The Macro.
First, you will need to open the Visual Basic Editor. There are two ways to do this. Either by hitting ALT +F11 or selecting the Developer Tab | Code Group | Visual Basic. Both methods have the same result.
If you cannot see the Developer Tab you may need to enable it. Check out my very short YouTube video for instructions to do that.
Starting The Macro.
So, I have inserted a New Module into my current Workbook. Type Sub then the name of the Excel macro. In this example, I have named my Macro WorksheetsToPDF().
[stextbox id=’info’]
Sub WorksheetsToPDF()
End Sub
[/stextbox]
As you type the name of the Macro and hit return, Excel will automatically insert End Sub. Now, all that is needed is the rest of the code that will export the individual worksheets in my workbook to separate PDF files.
Declaring Variables.
First, any variables need to be declared in this VBA code. this simply means we create a sort of memory container for these values. In this instance, we are declaring that ws as Worksheet.
[stextbox id=’info’]
Dim ws as Worksheet
[/stextbox]
Using The For Each Loop.
Next, we use the For Each loop. This method executes a statement or group of statements for each element in an array or collection. This is exactly what we need. We need to loop through each of the worksheets in the Excel workbook. We then need to export each of them to a separate PDF file.
[stextbox id=’info’]
For Each ws In Worksheets
ws.ExportAsFixedFormat xlTypePDF, “C:\YOUR FILEPATH\” & ws.Name & “.pdf”
Next ws
[/stextbox]
Just replace the “C:\YOUR FILEPATH\ with the file path of your choice where you want o save your freshly created PDF files.
Ending The Macro.
Finally, once of all of the worksheets have been exported, then the code finally ends. This line of code already exists as was already entered into the module when started the type the name of the Macro.
[stextbox id=’info’]
End Sub
[/stextbox]
Save Worksheets As Separate PDF files. Testing The Macro!.
So, let’s finally test our macro. We should end up with three separate PDF files named Areas A, B, and C with the PDF extension. Yes, it works. How easy is that?
So, if you want more Excel and VBA tips then sign up for my Monthly Newsletter. I share 3 Tips on the first Wednesday of the month. You will receive my free Ebook, 30 Excel Tips and check out all of my Macro Monday posts below.