VBA - Create new folder for sheets saved to pdf

[csimonds]

Hello,

I have the below code that saves my required sheets as pdf to current folder. However I would like it to create a new folder in the current directory which will have a set text name.

Any assistance would be most appreciated.

Thank you

Sub SaveWorksheetsAsPDFs()    Dim sFile       As String
    Dim sPath       As String
    Dim wks         As Worksheet


    With ActiveWorkbook
        sPath = .Path & "\"
        For Each wks In .Worksheets
            If LCase(wks.Range("N2").Value) = "print" Then
                sFile = wks.Range("A6").Value & ".pdf"
                wks.ExportAsFixedFormat Type:=xlTypePDF, _
                                        Filename:=sPath & sFile, _
                                        Quality:=xlQualityStandard, _
                                        IncludeDocProperties:=False, _
                                        IgnorePrintAreas:=False, _
                                        OpenAfterPublish:=False
            End If
        Next wks
    End With
End Sub

 

[jsotola]

here you go

thank you for taking the time to present your code in a legible format

it makes it so much easier just to look at it on the webpage

Sub SaveWorksheetsAsPDFs()


    Dim sFile       As String
    Dim sPath       As String
    Dim wks         As Worksheet
 
    With ActiveWorkbook
        sPath = .Path & "\[COLOR=#ff0000]test123\[/COLOR]"
[COLOR=#ff0000]        MkDir sPath[/COLOR]
        For Each wks In .Worksheets
            If LCase(wks.Range("N2").Value) = "print" Then
                sFile = wks.Range("A6").Value & ".pdf"
                wks.ExportAsFixedFormat Type:=xlTypePDF, _
                                        Filename:=sPath & sFile, _
                                        Quality:=xlQualityStandard, _
                                        IncludeDocProperties:=False, _
                                        IgnorePrintAreas:=False, _
                                        OpenAfterPublish:=False
            End If
        Next wks
    End With
End Sub

 

[csimonds]

Hi, thanks for taking the time to reply, appreciate the assistance.
I have tried the suggested, it creates the folder but does not save the files into the folder - saves it outside.

Any thoughts?

Thank you

here you go

thank you for taking the time to present your code in a legible format

it makes it so much easier just to look at it on the webpage

Sub SaveWorksheetsAsPDFs()


    Dim sFile       As String
    Dim sPath       As String
    Dim wks         As Worksheet
 
    With ActiveWorkbook
        sPath = .Path & "\[COLOR=#ff0000]test123\[/COLOR]"
[COLOR=#ff0000]        MkDir sPath[/COLOR]
        For Each wks In .Worksheets
            If LCase(wks.Range("N2").Value) = "print" Then
                sFile = wks.Range("A6").Value & ".pdf"
                wks.ExportAsFixedFormat Type:=xlTypePDF, _
                                        Filename:=sPath & sFile, _
                                        Quality:=xlQualityStandard, _
                                        IncludeDocProperties:=False, _
                                        IgnorePrintAreas:=False, _
                                        OpenAfterPublish:=False
            End If
        Next wks
    End With
End Sub

 

[dmt32]

Try changing Dir to folder just created & see if that helps.
Also, may be good idea to wrap MkDir in an On error statement just in case Folder already exits:

On Error Resume Next
    MkDir sPath
    On Error GoTo 0
    ChDir sPath

Dave