Dynamic file path in VBA

[Gorandulac32]

I have a macro that essentially takes all files in a folder path and creates a master file with each file being named as a tab on the master file macro.

For example the folder in the path below has files called 0701file, 0702file, 0703file which all get created as tabs. My issue is that i need to change the bolded 07 every month for the macro to work. I guess at year end I would run into the same same issue. My question is how do I make the file path dynamic so i can pick the year and the month, since those are the only variables that would change in the path.

Thanks

—————————————————————————————————————————————————————————————————————————


Sub MergeWorkbooks()

Dim FolderPath As String
Dim File As String
Dim i As Long


FolderPath = "C:\Users\Goran\2021\07

File = Dir(FolderPath)

Do While File <> ""

Workbooks.Open FolderPath & File
ActiveWorkbook.Worksheets(1).Copy _
after:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
ActiveSheet.Name = Replace(File, ".xlsx", "")
Workbooks(File).Close

File = Dir()

Loop

For i = 1 To 12

Worksheets(MonthName(i, True)).Move after:=Worksheets(Worksheets.Count)

Next


End Sub

 

[steve the fish]

Answer is it depends. You could have the values in a couple of cells. You could assign it to this month and this year. Depends what you need.