Automatically Save Excel Sheets as PDFs with Continuous Numbering Using VBA

Automatically Save Excel Sheets as PDFs with Continuous Numbering Using VBA

Have you ever needed to save multiple sheets in an Excel workbook as separate PDF files with continuous numbering? Doing this manually can be time-consuming and prone to errors. With VBA, you can automate this process and ensure each PDF is correctly numbered. In this post, I'll show you how to set up your workbook and write the VBA code to achieve this.

Step-by-Step Guide

Step 1: Setting Up the Workbook

First, let's set up our workbook. In this example, I have a sheet called "Sign In" and I have this form from 1 to 20. I want to achieve 1. print this excel to pdf, 2. Change C5 to 2, then print again, then 3. Change C5 to 3 and until C5 reaches F5 number which is 20

Step 2: Writing the VBA Code

Next, we'll write the VBA code. Follow these steps:

1. Open the VBA editor by pressing Alt + F11.

2. Insert a new module by clicking Insert > Module.

3. Copy and paste the following code into the module

Write your text here...

Option Explicit

Sub SaveAsPDF()
Dim i As Integer
Dim ws As Worksheet
Dim pdfName As String
Dim folderPath As String

' Disable screen updating
Application.ScreenUpdating = False

' Set the worksheet
Set ws = ThisWorkbook.Sheets("Sign_in")

' Define the folder path to save the PDFs
folderPath = "C:\Users\XXX\Desktop\Sign-in\"
' Change this to your desired folder path

' Loop through the specified range
For i = ws.Range("C5").Value To ws.Range("F5").Value
ws.Range("C5").Value = i

' Create the PDF filename
pdfName = folderPath & "Sign_IN_" & i & ".pdf"

' Export the range as a PDF

ws.Range("A1:H47").ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdfName, Quality:=xlQualityStandard

Next i

' Re-enable screen updating
Application.ScreenUpdating = True

End Sub

This could be time consuming if we are doing it manually

Step 3: Run the Code

Next, run the code and enjoy the benefit of VBA!