VBA to make filepath dynamic

[Gorandulac32]

So the below code works to go into a file path C:\Users\Goran\2021\06 and takes all the files from that file path and combines them into 1 file and the contents of each file are a separate tab. For example 0601file, 0602file from the folder get created as a tab on the master file. The question is how do i dynamically account for the month change. The code I have below works, but i would need to change the date manually each time and then run the macro.



Sub MergeWorkbooks()

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


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

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

 

[Fluff]

How about

FolderPath = "C:\Users\Goran\" & year(date) & "\" & format(date,"mm")

 

[Gorandulac32]

I get an error on Worksheets(MonthName(i, True)).Move after:=Worksheets(Worksheets.Count)

run-time error ’9’

 

[johnnyL]

@Gorandulac32 That line is from your original code that you said works.

 

[Gorandulac32]

Yes it works in my original code but it doesn’t work in the dynamic formula adjusted one you suggested. I guess what code would I need to go to a folder and pull each file and create a master file with each file as a separate tab.

thanks

 

[Fluff]

That has nothing to do with your original question, so you need to start a new thread for that question. Thanks