Hello Excellers and welcome back to my regular blog spot of #macromonday. Today, I want to show you how to write a simple Excel VBA macro which will convert to PDF then export and save each of your individual worksheets in your Excel workbook separate PDF files. I have used this macro a good few times myself and it has saved me a tonne of time. It is really useful to generate individual reports for sales areas, months of the year or products. In my example, I have 12 worksheets one for each month of the year sales figures. I will show you how to export and save them all individual monthly reports.
What Does The Macro Do?
This simple macro exports all of the individual worksheets in an Excel workbook as individual PDF files. We use the Worksheet.ExportAsFixedFormat Method which allows us to specify we want to export our new individual files as a file type of PDF.
How Does The Macro Work?
Step 1. Open the Visual Basic Editor. You can do this by either selecting the Developer Tab | Code | Visual Basic. Alternatively, you can hit ALT+F11 as a shortcut to open the Visual Basic Editor.
Step 2. Insert a New Module into your workbook.
Step 3. Write our code!. I have named the Macro ExportToPDF.
Step 4. We need to declare a variable.
ws as Worksheet
This simply created a memory container in Excel for this value.
Step 5. We use the For Loop in VBA. It one of the most frequently used loops in VBA. The For loop has two forms: For Next and For Each In Next. The For loop is typically used to move sequentially through a list of items or numbers. In this case, we instruct Excel to loop through all of the worksheets in the Excel workbook and export them with the Worksheet.ExportAsFixedFormat Method as a PDF.
Step 6. Once all of the worksheets have been exported the routine ends.
What Next? Want More Tips?
So, if you want more top tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
Do You Need Help With An Excel Problem?.
I am pleased to announce I have teamed up with Excel Rescue, where you can get help with Excel FAST.
