Create a folder and save a specific sheet in PDF format

[sofas]

Hello how can I create a new folder and save a certain sheet inside it as PDF and name it with a specific cell value. Taking into account that I copy the data several times, each time I must create a new folder in case it does not already exist with the name of cell B5

Private Sub CommandButton1_Click()

Dim ws As Worksheet
Dim strPath As String
Dim myFile As Variant
Dim strFile As String
On Error GoTo errHandler

Set ws = Sheet4


'enter name and select folder for file
' start in current workbook folder
strFile = ThisWorkbook.Path & "\" & strFile

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

If myFile <> "False" Then
ws.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=myFile, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False

MsgBox "done"
End If

exitHandler:
Exit Sub
errHandler:
MsgBox "Error"
Resume exitHandler
End Sub

 

[Anthony47]

So first you need to check if the subdirectory named in B5 of the activesheet(??) exists, and if "Not" then create it; then go on with the export code.
Try adding this block:

Set ws = Sheet4
'ADDED BLOCK >>
strFile = ThisWorkbook.Path & "\" & Range("B5").Value    'Target Path
If Dir(strFile, vbDirectory) = "" Then
    MkDir strFile
    MsgBox "Created " & strFile
Else
    MsgBox "Directory " & strFile & " already there"
End If
'<<<END ADDED BLOCK
'Continue with your code:
myFile = Application.GetSaveAsFilename(InitialFileName:=strFile, _
 FileFilter:="PDF Files (*.pdf), *.pdf", _
 Title:="Select Folder and FileName to save")

If myFile <> False Then                       '!!!  Modified
'etc etc

Note the line marked !!! that need to be mofied as shown
The 2 MsgBox are optional, of course

Try...

 

[footoo]

Duplicate post :

Save a specific range of excel sheet in pdf format in a new folder

Welcome. I want to create a new folder, name it and save a specific excel sheet. Include it in pdf format in the same path as the main file.

www.mrexcel.com