Move VBA created PDF to new folder

[bobkap]

I have created a macro that creates several different worksheets. I'm using the code below to output each of these newly-created sheets to a .PDF which is then stored in the same folder as the workbook.

I'd like to move these new sheets to a new folder created by VBA. This is payroll data that needs to be run twice per month. Ideally, I'd like to create some kind of folder name that has a constant name and then a variable ending showing the date. For example: payroll 091518.

Any help would be GREATLY appreciated as I've tried for days to figure this out myself and just decided to give in and ask for help.

Set wb = ThisWorkbook


For Each sh In wb.Worksheets


sh.Select


pdf_name = sh.Name & ".pdf"

If sh.Name <> "Input" And _
sh.Name <> "Mileages" And _
sh.Name <> "Payrates" And _
sh.Name <> "Rates" Then
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:="Instructor Pay For" & " " & pdf_name, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=True

End If
Next
'

 

[mole999]

You can use Environ to navigate a number of System paths \DESKTOP\ etc

You could have a reference page that you update for each session with the main Path stored and use that as a starting point

the date is a simple conversion of a standard date using =TEXT(Cell,"ddmmyy")

Then its a matter of string the bits together

Dim WorkPath as string
Dim Worklocation
Worklocation = range("cell for your main path")
Dim WorkDate = range("cell for the date output")

Workpath = Worklocation & WorkDate construct as necessary to pull the final destination string together, like the Filename

 

[davesexcel]

Test it out, the folder "C:\Test2" has to exist, the code will then place a new folder in that directory.

Sub Button1_Click()
    Dim wb As Workbook, sh As Worksheet
    Dim d As Date, s As String, PdF_naMe As String, NwDir As String

    Set wb = ThisWorkbook
    d = Now
    s = Format(d, "mmddyy") & "-PayRoll"
    NwDir = "C:\Test2\" & s & "\"

    If Dir(NwDir, vbDirectory) = "" Then
        MkDir "C:\Test2\" & s
    End If

    For Each sh In wb.Worksheets

        PdF_naMe = sh.Name & ".pdf"

        If sh.Name <> "Input" And sh.Name <> "Mileages" And _
           sh.Name <> "Payrates" And sh.Name <> "Rates" Then

            sh.ExportAsFixedFormat Type:=xlTypePDF, Filename:=NwDir & PdF_naMe

        End If
    Next
End Sub