VBA Help: How to dynamically change two folder paths based on a cell value, and save the copied sheets without formulas?

[nmartin]

Hi, I found the below VBA code below and it works but I would like to change the last two folders that are highlighted in yellow dynamically based on cell values located on the NewPreShiftSheet, the year cell is located in O3 and the month is located in O2?

Sub Copy_Save_Lineup_Recap_New_Workbook()
'
' Copy_Save_Lineup_Recap_New_Workbook Macro
'


    Dim FName           As String
    Dim FPath           As String
    Dim NewBook         As Workbook

    FPath = "\\Daily Line Up\2020\3. Mar"
    FName = "Recap " & Format(Date, "m-d") & ".xlsx"

    Set NewBook = Workbooks.Add

    ThisWorkbook.Sheets(Array("NewPreShiftSheet", "Recap")).Copy Before:=NewBook.Sheets(1)

    If Dir(FPath & "\" & FName) <> "" Then
        MsgBox "File " & FPath & "\" & FName & " already exists"
    Else
        NewBook.SaveAs Filename:=FPath & "\" & FName
    End If

End Sub

 

[nmartin]

I was in a hurry.

Thanks in advance for helping to solve my issues.

Nick