Save Active Sheet As Multiple PDFs (Based on List of Cell Values) | Excel VBA Macro
Excel VBA Macro: Save Active Sheet As Multiple PDFs (Based on List of Cell Values). In this video, we go over how to extract a worksheet from an excel workbook multiple times and save the filtered data from that tab as a new PDF, based on the cell values of multiple items in a list, to a designated file path; saving multiple PDFs at once. We use our code to filter data and save the active worksheet as a pdf, and then repeat the process automatically so that the macro runs through all the regions in a list, filtering the data by each one, and saving each new PDF using the current region (cell value) in the file name.
Data used in this video:
https://gsociology.icaap.org/datauplo...
Code:
Sub list_of_pdfs()
Dim data As Worksheet
Dim list As Worksheet
Dim region As String
Dim count As Long
Dim i As Long
Set data = ThisWorkbook.Sheets(1)
Set list = ThisWorkbook.Sheets(2)
'count numnber of regions
list.Activate
count = WorksheetFunction.CountA(Range("A1", Range("A1").End(xlDown)))
data.Activate
For i = 1 To count
'updating the region name
region = list.Cells(i, 1).Text
data.Cells(2, 1) = region
'filter by current region
Range("A4").AutoFilter field:=2, Criteria1:=region
'save pdf
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:="C:\Users\greggowaffles\Documents\Youtube Videos\Test\" & _
"Regions of the World - " & region
Next i
ActiveSheet.AutoFilterMode = False
End Sub
#ExcelVBA #ExcelMacro
