Excel VBA code to Create Folders and Sub Folders

Click here to download the workbook used in the below example.

Copy below code and edit as per your requirement.

Sub Createfolder()

'www.comexcelhub.com
'This code is used to Create monthly Folders and Sub folders for each month folder
'This is filesystem object
Dim FSO As Object
Dim Mn_L_Row, Day_L_Row, Mn_lp, day_Lp As Long
Dim Ma_loct, Mn_Fldr, Day_Fldr As String
Dim Tbwk As Workbook
Dim sht1 As Worksheet

'This is to set value to FSO variable
Set FSO = CreateObject("scripting.filesystemobject")

'This is to set value to Tbwk variable
Set Tbwk = ThisWorkbook

'This is to set value to Sht1 variable
Set sht1 = Tbwk.Sheets("Sheet1")

'This is to get last row number for Months list
Mn_L_Row = sht1.Cells(Rows.Count, 2).End(xlUp).Row

'This is to get last row  number for days list
Day_L_Row = sht1.Cells(Rows.Count, 3).End(xlUp).Row

'This is to get main folder location
Ma_loct = sht1.Range("C5")

  'Here loop starts to check and create each month Folder
  For Mn_lp = 10 To Mn_L_Row
  
  'This is to create each month folder location and store value Mn_Fldr variable
  Mn_Fldr = Ma_loct & "\" & sht1.Range("B" & Mn_lp)
            
            'This is to Check and Create each month folder
            If FSO.FolderExists(Mn_Fldr) = True Then
                Else
                'This is to Create each month folder
                MkDir Mn_Fldr
            End If
            
                 'Here loop starts to check and create each day Folder
                 For day_Lp = 10 To Day_L_Row
                    
                    'This is to create each day folder location and store value Day_Fldr variable
                    Day_Fldr = Mn_Fldr & "\" & sht1.Range("C" & day_Lp)
                    
                    'This is to Check and Create each day folder
                    If FSO.FolderExists(Day_Fldr) = True Then
                        Else
                       'This is to Create each day folder
                        MkDir Day_Fldr
                    End If
                    
                'This is go to next day in the list
                 Next day_Lp
                 
    'This is go to next month in the list
    Next Mn_lp

End Sub

Explanation of Excel VBA code to Create Folders and Sub Folders

Code Title: Sub Createfolder()

Explanation Title Code: The title of the Macro is Createfolder.

Code Line 1: Dim FSO As Object
Code Line 2: Dim Mn_L_Row, Day_L_Row, Mn_lp, day_Lp As Long
Code Line 3: Dim Ma_loct, Mn_Fldr, Day_Fldr As String
Code Line 4: Dim Tbwk As Workbook
Code Line 5: Dim sht1 As Worksheet

Explanation Line 1 to 5: From Line 1 to 5 we are declaring variables used in the code.

Code Line 6: Set FSO = CreateObject("scripting.filesystemobject")
Code Line 7: Set Tbwk = ThisWorkbook
Code Line 8: Set sht1 = Tbwk.Sheets("Sheet1")

Explanation Line 6 to 8: From line 6 to 8 we are setting values to FSO, Tbwk and sht1 variables.

Code Line 9: Mn_L_Row = sht1.Cells(Rows.Count, 2).End(xlUp).Row

Explanation Line 9: In this line, we are calculating last row number for month list to loop each month.

Code Line 10: Day_L_Row = sht1.Cells(Rows.Count, 3).End(xlUp).Row

Explanation Line 10: In this line, we are calculating last row number for day list to loop each day for every month that is the reason we have placed the code inside the month loop.

Code Line 11: Ma_loct = sht1.Range("C5")

Explanation Line 11: In this line, we are assigning value to main location value update in the Excel Sheet in Cell C5 to Ma_loct variable.

Code Line 12: Ma_loct = sht1.Range("C5")

Explanation Line 12: In this line, we are assigning main location value updated in the Excel Sheet in Cell C5 to Ma_loct variable.

Code Line 13: For Mn_lp = 10 To Mn_L_Row

Explanation Line 13: In this line, we are starting loop to for each month.

Code Line 14: Mn_Fldr = Ma_loct & "\" & sht1.Range("B" & Mn_lp)

Explanation Line 14: In this line, we create each month folder location and store location path in Mn_Fldr variable.

Code Line 15: If FSO.FolderExists(Mn_Fldr) = True Then
Code Line 16: Else
Code Line 17: MkDir Mn_Fldr
Code Line 18: End If

Explanation Line 15 to 18: From line 15 to 18 we are checking if month folder is available or not if it is not available we are creating the folder.

Code Line 19: For day_Lp = 10 To Day_L_Row

Explanation Line 19: In this line, we are starting loop to for each day.

Code Line 20: Day_Fldr = Mn_Fldr & "\" & sht1.Range("C" & day_Lp)

Explanation Line 20: In this line, we create each day folder location and store location path in Day_Fldr variable.

Code Line 21: If FSO.FolderExists(Day_Fldr) = True Then
Code Line 22: Else
Code Line 23: MkDir Day_Fldr
Code Line 24: End If

Explanation Line 21 to 24: From line 21 to 24 we are checking if day subfolder is available or not if it is not available we are creating the folder.

Code Line 25: Next day_Lp

Explanation Line 25: In this line, we will go to the next day on the list. If the day list is completed then exit this loop.

Code Line 26: Next Mn_lp

Explanation Line 26: In this line, we will go to next month on the list. If the month list is completed then exit this loop.

Code Exit Line: End Sub

Explanation Exit Line: This code is to end the Excel macro (End Subroutine).

Click here to download the workbook used in the below example.

Contact to us