save selected worksheet as separate pdf file

[YOYO]

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]

Save sheet CT as a xlsx, xlsm, etc.?

Save all sheets in array to one pdf or one pdf each?

[YOYO]

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]

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

[YOYO]

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]

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) <> ""