MatthiasPBelmans
New Member
- Joined
- Mar 9, 2021
- Messages
- 1
- Office Version
- 365
- Platform
- Windows
Hi All,
Hope you can help me.
I have an excel with sheets that are activated when a value put in a cell.
I want to save only the activated, unhidden sheets to a PDF document.
I've tried this code, but it saves all the sheets and not the ones I activated...
Can anybody help me with this?
Hope you can help me.
I have an excel with sheets that are activated when a value put in a cell.
I want to save only the activated, unhidden sheets to a PDF document.
I've tried this code, but it saves all the sheets and not the ones I activated...
Can anybody help me with this?
VBA Code:
Sub PDFActiveSheet()
'Dim wsA As Sheets
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
On Error GoTo errHandler
Set wbA = ActiveWorkbook
arr = Array("commercial invoice", "shipping advice", "end use letter", "shipper declaration", "EXPORT CERT A")
Set wsA = Sheets(arr)
wsA.Select
strTime = Format(Now(), "yyyymmdd\_hhmm")
'get active workbook folder, if saved
strPath = wbA.Path
If strPath = "" Then
strPath = Application.DefaultFilePath
[URL='http://www.php.net/end']End[/URL] If
strPath = strPath & ""
'replace spaces and periods in sheet name
strName = Replace(Replace(Join(arr), " ", ""), ".", "_")
'create default name for savng [URL='http://www.php.net/file']file[/URL]
strFile = strName & "_" & strTime & ".pdf"
strPathFile = strPath & strFile
'use can enter name and
' select folder for [URL='http://www.php.net/file']file[/URL]
myFile = Application.GetSaveAsFilename _
(InitialFileName:=strPathFile, _
filefilter:="PDF Files (*.pdf), *.pdf", _
Title:="Select Folder and FileName to save")
'export to PDF if a folder was selected
If myFile <> "False" Then
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=myFile, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
'confirmation message with [URL='http://www.php.net/file']file[/URL] info
MsgBox "PDF file has been created: " _
& vbCrLf _
& myFile
[URL='http://www.php.net/end']End[/URL] If
exitHandler:
[URL='http://www.php.net/exit']Exit[/URL] Sub
errHandler:
MsgBox "Could not create PDF file"
Resume exitHandler
[URL='http://www.php.net/end']End[/URL] Sub