Excel PDF with multiple ranges

shophoney

Active Member
Joined
Jun 16, 2014
Messages
250
Hi,

I'm looking to create VBA in Excel to export multiple ranges to one PDF. The ranges can change with different criteria.

So far I have been able to write the following. But I will need to pull data from MULTIPLE TABS. The code below only works on one tab. How to I grab data for a few tabs.

Thanks

Sub BO_Sales_Summary_PDF()

'www.contextures.com
'for Excel 2010 and later
Dim wsA As Worksheet
Dim wbA As Workbook
Dim strTime As String
Dim strName As String
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant
Dim lastI As Long, lastQ As Long
Dim PDFranges As Range
On Error GoTo errHandler

With ActiveSheet
lastI = .Cells(.Rows.Count, "I").End(xlUp).Row
lastQ = .Cells(.Rows.Count, "Q").End(xlUp).Row
Set PDFranges = .Range("A2:I" & lastI & ",K2:Q" & lastQ)
End With

Set wbA = ActiveWorkbook
Set wsA = ActiveSheet
strTime = Format(Now(), "yyyymmdd\_hhmm")

strPath = "D:\Dropbox\Shared_Files\dana_jordan\Reports"
If strPath = "" Then
strPath = Application.DefaultFilePath
End If
strPath = strPath & "\"

strName = Replace(wsA.Name, " ", "")
strName = Replace(strName, ".", "_")

strFile = ActiveSheet.Range("A2").Value & " - " & strTime & ".pdf"
strPathFile = strPath & strFile

myFile = Application.GetSaveAsFilename _
(InitialFileName:=strPathFile, _
FileFilter:="PDF Files (*.pdf), *.pdf", _
Title:="Select Folder and FileName to save")

If myFile <> "False" Then
wsA.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=myFile, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
'confirmation message with file info
MsgBox "PDF file has been created: " _
& vbCrLf _
& myFile
End If

exitHandler:
Exit Sub
errHandler:
MsgBox "Could not create PDF file"
Resume exitHandler
End Sub
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,997
See if you can adapt my code here:
 

Forum statistics

Threads
1,141,062
Messages
5,704,054
Members
421,325
Latest member
tapete86

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top