How to Create Multiple Folders at Once from Excel

How to Launch the Visual Basic Editor in Excel

Click on Visual Basic under the Developer tab.

Launching Visual Basic Window

Insert a module to write the code.
Repeat this process for adding each Macro. (If you are using VBA for the first time, you may need to add the Developer tab to the ribbon in Excel).

Inserting Module to Write Code

How to Create Multiple Folders at Once from Excel: 2 Ways

Method 1 – Using the “MD” Function and NotePad to Create Multiple Folders at Once

The main folder names are documented inside column B.

Case 1.1 – Adding MD in Cells Using Flash Fill

Inside cell C5, insert the following

MD “Benjamin Johnson/Personal Information” “Benjamin Johnson/Complaints” “Benjamin Johnson/Accomplishments”

Writing Necessary Syntax to Create Folders and Subfolders

Select cells C5 to C9.
Under the Data tab, select Flash Fill. Selected cells will be filled with corresponding information.

Using Flash Fill Feature to Add MD Before Folder Names

Copy the cells from C5 to C9.
Open a text document.
Paste the information inside the document.

Pasting the Required Information Inside a Text Document

Save the file by pressing Ctrl + S.
Rename the document with a .bat extension.

Changing the Extension of the Text Document

Double-click on the file. The desired folders will be created inside the same location.

Desired Folders are Created

Three subfolders will also be created inside each of the folders.

Desired Subfolders are Created

Case 1.2 – Adding MD in Cells Using Formula

This method can’t make subfolders.

Inside cell C5, use the following formula and press Enter.

=”MD “&B5

9- Using Formula to Add “MD” Before Folder Names

AutoFill the formula down by dragging the Fill Handle from the bottom-right corner of the cell.

Using AutoFill for Rest of the Cells

Repeat the steps from the previous case once you finish the table.

Method 2 – Excel VBA to Create Multiple Folders at Once

Insert a VBA module.
Inside the module, paste the following code:

Code for how to Create Multiple Folders at Once from Excel

Code Syntax:

Sub CreateFoldersAndSubfolders()
Dim path As String
Dim mainFolderRange As Range
Dim subfolderNames As Variant
Dim i As Integer, j As Integer
'Change this to the desired path
path = "C:\Users\User\Desktop\SOFTEKO\Article 19\"
subfolderNames = Array("Personal Information", "Complaints", "Accomplishments")
'Change this to the range containing the names of the main folders
Set mainFolderRange = Range("B5:B9")
For i = 1 To mainFolderRange.Rows.Count
'Create main folder
MkDir path & mainFolderRange(i, 1).Value
'Create subfolders within main folder
For j = 0 To UBound(subfolderNames)
MkDir path & mainFolderRange(i, 1).Value & "\" & subfolderNames(j)
Next j
Next i
End Sub

Code Breakdown:

“Sub CreateFoldersAndSubfolders()” – defines a new VBA subroutine named “CreateFoldersAndSubfolders”.
“Dim path As String” – declares a string variable named “path” which will store the path where the folders will be created.
“Dim mainFolderRange As Range” – declares a range variable named “mainFolderRange” which will store the range of cells containing the names of the main folders.
“Dim subfolderNames As Variant” – declares a variant variable named “subfolderNames” which will store the names of the subfolders as an array.
“Dim i As Integer, j As Integer” – declares two integer variables named “i” and “j” which will be used as counters in the for-loops.
“path = “C:\Users\User\Desktop\SOFTEKO\Article 19″” – sets the value of the “path” variable to the desired path where the folders will be created.
“subfolderNames = Array(“Personal Information”, “Complaints”, “Accomplishments”)” – sets the value of the “subfolderNames” variable to an array of strings containing the names of the subfolders.
“Set mainFolderRange = Range(“B5:B9″)” – sets the value of the “mainFolderRange” variable to the range of cells containing the names of the main folders.
“For i = 1 To mainFolderRange.Rows.Count” – starts a for-loop which will iterate through the rows of the “mainFolderRange” variable.
“MkDir path & mainFolderRange(i, 1).Value” – creates a new folder with the name specified in the current row of the “mainFolderRange” variable at the specified path.
“For j = 0 To UBound(subfolderNames)” – starts a nested for-loop which will iterate through the elements of the “subfolderNames” array.
“MkDir path & mainFolderRange(i, 1).Value & “” & subfolderNames(j)” – creates a new subfolder with the name specified in the current element of the “subfolderNames” array inside the current main folder at the specified path.
“Next j” – ends the nested for-loop.
“Next i” – ends the main for-loop and completes the subroutine.
“End Sub” – ends the subroutine.

Run the code.

Things to Remember

Make sure to double-check the path where you want to create the folders to avoid any errors.
Ensure that the folder names do not contain any special characters or illegal characters that are not allowed in folder names.
Ensure that you have the necessary permissions to create folders at the specified path.

Download the Practice Book

Create Multiple Folders at Once.xlsm

<< Go Back to Create Folder in Excel | Excel Files | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!