Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

Save a Workbook as a Single PDF Using VBA

From our last post we learnt how to convert an entire workbook to a single PDF manually. If you want to know how, then please check this post.

Convert an Entire Workbook to a Single PDF File

Today I’m going to teach you how to do the same thing using VBA. So you can use this subroutine inside your VBA applications where necessary. Now let’s start developing the code. First we need to declare a few variables.

We need to loop through all the sheets of the workbook. So we need to declare one variable as worksheet.

Dim WS As Worksheet

Then we need an array to assign sheet names.

Dim SheetNames() As Variant

PDF file name will be assigned to a string type variable.

Dim PDF_FileName As String

In addition to those variables, let’s declare two more variables of type integer. One is to hold the number of sheets. And other variable is to use as a counter inside for next loop.

Dim NumberOfSheets As Integer
Dim Counter As Integer

Now we have declared all the required variables. After variable declaration we can calculate the number of sheets inside the workbook as our first step.

NumberOfSheets = ThisWorkbook.Worksheets.Count

Now we know the upper bound of our SheetNames array. So we can size that dynamic array using redim statement.

ReDim SheetNames(1 To NumberOfSheets)

As our next step we can loop through all the sheets of the workbook and assign name of each worksheet to the SheetNames array. We can do it as follows.

Counter = 1

For Each WS In Worksheets
     SheetNames(Counter) = WS.Name
     Counter = Counter + 1
Next WS

Now let’s assign a name to our PDF file. You can give any valid name to the PDF file.

PDF_FileName = "PDf file name here"

Next we use SheetNames array to select all the sheets.

Sheets(SheetNames).Select

Then we can convert all the sheets to one single PDF file as follows.

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"D:\Work\Create PDF\" & PDF_FileName & ".pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
False

You should replace "D:\Work\Create PDF\" with path of your folder where you need to save the PDF file. Or else you can assign the folder path to a variable and then use that inside the code like this.

Dim FolderPath as string

FolderPath = "D:\Work\Create PDF"

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
FolderPath & "\" & PDF_FileName & ".pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
False

Here is the complete subroutine.

Sub ConvertWorkbookToSinglePDF()

Dim WS As Worksheet

Dim SheetNames() As Variant

Dim PDF_FileName As String

Dim NumberOfSheets As Integer
Dim Counter As Integer

NumberOfSheets = ThisWorkbook.Worksheets.Count

ReDim SheetNames(1 To NumberOfSheets)

Counter = 1

For Each WS In Worksheets
     SheetNames(Counter) = WS.Name
     Counter = Counter + 1
Next WS

PDF_FileName = "PDf file name here"

Sheets(SheetNames).Select

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"D:\Work\New Post 2\" & PDF_FileName & ".pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
False

End Sub

Popular Posts

Contact Form

Name

Email *

Message *