View Full Version : save selected worksheet as separate pdf file
Hi, I have the code for save worksheet as pdf file, but its not separate file, and i couldn't use the worksheet name as part of the file name. AND one of the selected worksheet need to be saved as pdf and excel file, (for example: "CT")
Please HELP!
Sub ExportAsPDF()
Dim FolderPath As String
Dim Filename As String
Dim ws As Worksheet
FolderPath = "X:\PERSONAL FOLDERS\test folder\Monthly"
Sheets(Array("January", "February", "CT", "EM")).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FolderPath & "WORKBOOK" & ws.Name, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
ignoreprintareas:=False
MsgBox "All PDF's have been successfully exported."
End Sub
Kenneth Hobs
10-26-2017, 10:00 AM
Save sheet CT as a xlsx, xlsm, etc.?
Save all sheets in array to one pdf or one pdf each?
Hi, all other selected worksheet will save as PDF only, but worksheet "CT" will save as PDF and xlsx.
save all sheets in array separately, so it will be one pdf for each selected worksheet.
Thanks!
Save sheet CT as a xlsx, xlsm, etc.?
Save all sheets in array to one pdf or one pdf each?
Kenneth Hobs
10-26-2017, 01:02 PM
Sub ExportAsPDF()
Dim FolderPath$, Filename$, ws As Worksheet, a, e
FolderPath = "X:\PERSONAL FOLDERS\test folder\Monthly\"
a = Split("January,February,CT,EM", ",")
For Each e In a
Worksheets(e).ExportAsFixedFormat xlTypePDF, FolderPath & e & ".pdf", _
xlQualityStandard, True, False
Next e
Set ws = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
ThisWorkbook.Worksheets("CT").Copy after:=ws
Application.DisplayAlerts = False
Worksheets(1).Delete
ActiveWorkbook.SaveAs FolderPath & "CT.xlsx", xlOpenXMLWorkbook
Application.DisplayAlerts = True
ActiveWorkbook.Close False
MsgBox "All PDF's have been successfully exported."
End Sub
Hi Kenneth, Thanks for your reply. it shows a "Run=time error'9': Subscript out of range" Do you know why is that?
also, right now i am using this updated code that can save all the visible worksheet separately. but every time when i add the code for sheets(array) , nothing happens.
maybe you can take a look at the codes below and see if you can update it ?
Thanks a lot!
Sub createPDFfiles()
Dim ws As Worksheet
Dim fname As String
For Each ws In ActiveWorkbook.Worksheets
On Error Resume Next
fname = "X:\PERSONAL FOLDERS\test folder\Monthly\" & "workbook " & ws.Name
ws.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=fname, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False
Next ws
MsgBox "All PDF's have been successfully exported."
End Sub
Kenneth Hobs
10-27-2017, 07:49 AM
Please paste code between code tags. Click the # icon in the reply toolbar to insert the tags.
Maybe one of your worksheets are hidden? Maybe the worksheet's tab name is not a valid filename? Maybe the folder does not exist? When debugging, comment out On Error lines. Debug.Print puts the results into the Immediate Window. Press Ctrl+G after a run to see the results. Always Compile in the Debug menu before a Run.
e.g.
fname = "X:\PERSONAL FOLDERS\test folder\Monthly\workbook " & ws.Name
Debug.Print fname, ws.Name, ws.Visible
Debug.Print Dir("X:\PERSONAL FOLDERS\test folder\Monthly\", vbDirectory) <> ""
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.