VBA & Macro Problems

Excel VBA: Create a Folder and Save a Worksheet as a New Workbook

Jason MillerJason Miller Nov 18, 2026 868 views

Question details

Create a folder from a name on a register sheet and save a copied worksheet in it as a new Excel workbook.

Create a Folder and Save a Worksheet as a New Workbook with Excel VBA
Product
Microsoft Excel VBA
Device & OS
not provided
Scenario
An Excel macro reads a base path, folder name, and file name from a register sheet, then copies a template worksheet into a new workbook.
Observed behavior
The destination folder is created if needed, and the copied worksheet is saved there as an XLSX file.
Before you start

In Excel, prepare a sheet named Register and a worksheet named Template in the macro workbook. The example below is for Excel on Windows; enter an existing base folder path in Register!B2, a folder name in B3, and a file name in B4.

Solution 1Recommended

Use VBA to create the folder and save a copied worksheet

Use this Windows Excel macro when the register and template sheets are in the workbook containing the code.

The macro checks the names and base path, creates the destination folder only if necessary, and refuses to overwrite an existing file. It uses the Windows FileSystemObject through late binding, so no VBA reference needs to be added.

1
Enter the register values

On the Register sheet, enter an existing path such as C:\Reports in B2, a folder name such as Project A in B3, and a file name such as Summary.xlsx in B4. If B4 has no .xlsx extension, the macro adds it.

2
Add the VBA code

Press Alt+F11, select the macro workbook, and choose Insert > Module. Paste the following code into the module: Option Explicit Sub SaveTemplateToFolder() Dim reg As Worksheet, tempBook As Workbook Dim fso As Object Dim basePath As String, folderName As String, fileName As String Dim folderPath As String, savePath As String On Error GoTo Failed Set reg = ThisWorkbook.Worksheets("Register") Set fso = CreateObject("Scripting.FileSystemObject") basePath = Trim$(CStr(reg.Range("B2").Value)) folderName = CStr(reg.Range("B3").Value) fileName = CStr(reg.Range("B4").Value) If Not fso.FolderExists(basePath) Then Err.Raise vbObjectError + 1, , "The base folder does not exist." End If If Not ValidWindowsName(folderName) Or Not ValidWindowsName(fileName) Then Err.Raise vbObjectError + 2, , "Enter valid folder and file names." End If If LCase$(Right$(fileName, 5)) <> ".xlsx" Then fileName = fileName & ".xlsx" folderPath = fso.BuildPath(basePath, folderName) If fso.FileExists(folderPath) Then Err.Raise vbObjectError + 3, , "A file already uses the destination folder name." End If If Not fso.FolderExists(folderPath) Then MkDir folderPath savePath = fso.BuildPath(folderPath, fileName) If fso.FileExists(savePath) Or fso.FolderExists(savePath) Then Err.Raise vbObjectError + 4, , "The destination already exists. Choose another file name." End If ThisWorkbook.Worksheets("Template").Copy Set tempBook = ActiveWorkbook tempBook.SaveAs Filename:=savePath, FileFormat:=xlOpenXMLWorkbook tempBook.Close SaveChanges:=False Set tempBook = Nothing MsgBox "Saved: " & savePath, vbInformation Exit Sub Failed: Dim message As String message = Err.Description On Error Resume Next If Not tempBook Is Nothing Then tempBook.Close SaveChanges:=False MsgBox "Could not save the workbook: " & message, vbExclamation End Sub Private Function ValidWindowsName(ByVal value As String) As Boolean Dim i As Long, ch As String, stem As String If Len(value) = 0 Then Exit Function If value <> Trim$(value) Or Right$(value, 1) = "." Then Exit Function For i = 1 To Len(value) ch = Mid$(value, i, 1) If AscW(ch) >= 0 And AscW(ch) < 32 Then Exit Function If InStr(1, "<>:""/\|?*", ch, vbBinaryCompare) > 0 Then Exit Function Next i stem = UCase$(Split(value, ".")(0)) Select Case stem Case "CON", "PRN", "AUX", "NUL", "COM1", "COM2", "COM3", "COM4", "COM5", "COM6", "COM7", "COM8", "COM9", "LPT1", "LPT2", "LPT3", "LPT4", "LPT5", "LPT6", "LPT7", "LPT8", "LPT9" Exit Function End Select ValidWindowsName = True End Function

3
Run the macro

Save the source workbook as an Excel Macro-Enabled Workbook (.xlsm). In Excel, open Developer > Macros, select SaveTemplateToFolder, and click Run. Check the success message and open the new file in the destination folder.

Use VBA to create the folder and save a copied worksheet
XLSX does not retain VBA code: The new file is saved as .xlsx, which cannot retain VBA macros. Copying a sheet can also leave formulas linked to the source workbook; check the output if the template refers to other sheets.
Free Microsoft Office alternative

Work with Excel-compatible spreadsheets in WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for Excel formats, making it easy to move existing workbooks into WPS Spreadsheets. It does not fix the Excel VBA macro; use Excel for the automation shown above.

Open and edit Microsoft Excel XLSX workbooks.Work with worksheets and save spreadsheet files through a familiar interface.Use a free, lightweight office suite for everyday spreadsheet tasks.Move existing Office files into a familiar workspace.
microsoft office alternative - wps office

Frequently Asked Questions

Why does MkDir fail when creating the destination folder?

MkDir creates one folder at a time and requires its parent folder to exist. Check the path in Register!B2 and make sure you have permission to create a folder there.

What happens if the destination workbook already exists?

The example checks the destination before SaveAs and stops instead of overwriting it. Change the name in Register!B4 or move the existing file, then run the macro again.

Can the saved workbook contain macros?

Not in the .xlsx format used here. To retain VBA in an output workbook, use an appropriate macro-enabled format and test that the required code is present in the new workbook.

How do I copy a different worksheet?

In the VBA module, replace "Template" in ThisWorkbook.Worksheets("Template").Copy with the exact name of the worksheet you want to copy.