VBA to Create Multiple Files in a Folder

You may need to create multiple workbooks in a folder, which can be time-consuming if done manually.

In this tutorial, however, I will show you how to do this quickly and efficiently using Excel VBA.

Example #1: Create Multiple Files in a Folder With Names in a Range

Let’s say you want to create 12 workbooks with the names January to December in a specific folder.

You can use the steps below to achieve that:

  1. Enter the month names in a range as depicted below:
  1. Enter the following VBA code in a standard module:
Sub CreateFilesNamesInRange()
Dim filePath As String
Dim i As Long
Dim fileName As String
Application.ScreenUpdating = False
filePath = "C:\Monthly Sales\"
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
fileName = Cells(i, 1).Value & ".xlsx"
With Workbooks.Add
.SaveAs filePath & fileName
.Close False
End With
Next
Application.ScreenUpdating = True
End Sub
  1. Ensure the worksheet containing the month names is active. Then, place the cursor inside the code and press F5 to execute it.

The code runs and creates multiple workbooks with the names January to December in the specified folder:

Explanation of the Code

The code declares two variables of the String data type and one variable of the Long data type. It then turns off screen updating to speed up the code’s execution.

The code specifies the folder where it will save the files and loops through each cell in the range containing the month names, concatenating the value in the cell with the ‘.xlsx’ extension to construct a workbook name.

At each loop iteration, the code creates a new workbook, saves it with its constructed name, and closes it without saving changes.

Finally, the code restores the screen updating to its default state.

You can customize the code to your requirements by changing the names in the cell range, file path, file extension, and so on.

Also read: Delete Files in a Folder Using VBA in Excel

Example #2: VBA to Create Multiple Files With Names in an Array

You can use Excel VBA to create multiple workbooks and save them with names in an array.

You can use the following code to create multiple workbooks in a folder and save them with the names of five states in an array:

Sub CreateWorkbooksWithNamesInArray()
Dim folderPath As String
Dim states() As Variant
Dim i As Long
Dim newWorkbook As Workbook
states = Array("Wyoming", "Alaska", "Montana", "Idaho", "Vermont")
folderPath = "C:\States Staff\"
Application.ScreenUpdating = False
For i = LBound(states) To UBound(states)
  Set newWorkbook = Workbooks.Add
  newWorkbook.SaveAs folderPath & states(i) & ".xlsx"
  newWorkbook.Close False
Next i
Application.ScreenUpdating = True
End Sub

When you execute the code, it creates five workbooks with the names of five states in the specified folder:

Explanation of the Code

The code assigns an array containing the names of five states to the ‘states’ variable and specifies the folder path where it will save the workbooks. To speed up code execution, it turns off screen updating.

The subroutine loops through each state in the ‘states’ array. Inside the loop, it creates a new workbook using the ‘Workbooks.Add’ method and saves it in the specified folder with a filename constructed by concatenating the state name and the ‘.xlsx’ extension.

It closes each workbook without saving any changes.

Once the code has created all workbooks, it re-enables screen updating to restore normal behavior.

Also read: How to Open Excel Files Using VBA

Example #3: Create Multiple Files Using Names of Worksheets

You can use Excel VBA code to create multiple files based on the worksheets in the workbook and save the files in a specific folder.

Suppose you have a workbook named ‘Regional Sales’ with four worksheets: ‘North Region,’ ‘East Region,’ ‘South Region,’ and ‘West Region.’

You can use the Excel VBA code below to create multiple files based on the worksheets in the workbook and save them in a specific folder:

Sub CreateFilesBasedOnWorksheets()
Dim wb As Workbook
Dim newWB As Workbook
Dim ws As Worksheet
Dim savePath As String
Dim fileName As String
savePath = "C:\Regional Sales\"
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each ws In ThisWorkbook.Worksheets
Set newWB = Workbooks.Add
ws.Copy Before:=newWB.Sheets(1)
Do While newWB.Sheets.Count > 1
    newWB.Sheets(2).Delete
Loop
fileName = savePath & ws.Name & ".xlsx"
newWB.SaveAs fileName
newWB.Close SaveChanges:=False
Next ws
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

When you execute the code, it creates multiple files based on the worksheets in the active workbook and saves them in the specified folder:

Explanation of the Code

The code loops through each worksheet in the active workbook and creates a new workbook for each worksheet. It then copies the worksheet to the new workbook, saves the new workbook with the worksheet name, and then closes it.

The code turns off screen updating and display alerts to speed up code execution and turns them back on once all the workbooks have been created.

Also read: VBA to Loop Through Files in a Folder

Example #4: Prompt User for Folder and Create Multiple Files

Sometimes, instead of hardcoding the file path, it’s better to prompt the user to select the folder where the code will create the files.

The following example code prompts the user to select the folder in which to save the ten workbooks that it will create:

Sub SelectFolderCreateFiles()
Dim i As Integer
Dim filePath As String
Dim folderPath As String
Dim excelApp As Object
Dim excelWorkbook As Object
Set excelApp = CreateObject("Excel.Application")
excelApp.Visible = False
With Application.FileDialog(msoFileDialogFolderPicker)
    .Title = "Select a Folder"
    .AllowMultiSelect = False
    If .Show <> -1 Then Exit Sub
    folderPath = .SelectedItems(1) & "\"
End With
For i = 1 To 10
    Set excelWorkbook = excelApp.Workbooks.Add
    filePath = folderPath & "Workbook" & i & ".xlsx"
    excelWorkbook.SaveAs filePath
    excelWorkbook.Close False
Next i
excelApp.Quit
Set excelWorkbook = Nothing
Set excelApp = Nothing   
End Sub

When you execute the code, it sends a dialog box to the screen prompting the user to select a folder:

When the user selects a folder and clicks OK, the code creates ten workbooks and saves them in the selected folder:

Explanation of the Code

 The subroutine first creates a new instance of Excel, prompts the user to select a folder, then creates ten new workbooks, saves them with the desired file name and extension in the selected folder, and finally closes the Excel application.

Also read: VBA Check If Workbook Is Open

Example #5: Create a Folder and then Create Multiple Files in that Folder

Sometimes, you may want the Excel VBA code to create a folder to save the files it creates.

The code below first checks if a folder exists. If it doesn’t, as is the case in this example, it creates one, generates five new workbooks, and saves them in the newly created folder:

Sub CreateWorkbooksInCreatedFolder()
Dim folderPath As String
Dim newFolderPath As String
Dim i As Integer
Dim wb As Workbook
folderPath = "C:\User Guides\"
If Dir(folderPath, vbDirectory) = "" Then
    MkDir folderPath
End If
For i = 1 To 5
    Set wb = Workbooks.Add
    wb.SaveAs folderPath & "Guide_" & i & ".xlsx"
    wb.Close False
Next i
End Sub

When you execute the code, it creates a folder named ‘User Guides,’ creates five workbooks, and saves them in the newly created folder:

In this tutorial, I showed you five examples of VBA for creating multiple files in a folder.

I hope you found the tutorial helpful.

Other Excel VBA articles you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.