VBA & Macro Problems

Copy a Range and Build a Folder Path with an Excel VBA Macro

Huda QurayshiHuda Qurayshi Nov 18, 2026 870 views

Question details

Update an Excel VBA macro to copy a range and save a station workbook in folders based on worksheet values.

Copy a Range and Build a Folder Path with an Excel VBA Macro
Product
Microsoft Excel VBA
Device & OS
not provided
Scenario
Create a station workbook from STASH!A53:I54 and organize it by date, city, and address from the FORMULAS sheet.
Observed behavior
The desired workbook contains the copied values and formats and is saved as STATION.xlsx in the corresponding folder path.
Before you start

Check that the workbook contains sheets named STASH and FORMULAS, with the city in FORMULAS!B42 and address in FORMULAS!B43. Identify the date cell on FORMULAS; the macro will ask you to select it.

Solution 1Recommended

Use VBA to create the folders and save the copied range

Use this Windows Excel macro to copy the range as values and formats and save it under Desktop\ASBUILT\date\city\address.

The date cell was not specified, so the macro prompts you to select a single cell on FORMULAS. It creates missing folders before saving and replaces characters that Windows does not allow in folder names.

1
Open the VBA editor

In Excel, open the source workbook, press Alt+F11, and choose Insert > Module.

2
Add the macro

Paste the following code into the module: Sub CreateStationWorkbook() Dim sourceBook As Workbook, stationBook As Workbook Dim stash As Worksheet, formulas As Worksheet Dim dateCell As Range Dim folderPath As String, city As String, address As String Set sourceBook = ThisWorkbook Set stash = sourceBook.Worksheets("STASH") Set formulas = sourceBook.Worksheets("FORMULAS") formulas.Activate On Error Resume Next Set dateCell = Application.InputBox("Select the date cell on FORMULAS:", Type:=8) On Error GoTo 0 If dateCell Is Nothing Then Exit Sub If Not dateCell.Worksheet Is formulas Or dateCell.CountLarge <> 1 Then MsgBox "Select one date cell on FORMULAS." Exit Sub End If If Not IsDate(dateCell.Value) Then MsgBox "The selected cell must contain a valid date." Exit Sub End If city = FolderName(CStr(formulas.Range("B42").Value)) address = FolderName(CStr(formulas.Range("B43").Value)) If city = "" Or address = "" Then MsgBox "Enter a valid city and address on FORMULAS." Exit Sub End If folderPath = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\ASBUILT" If Dir(folderPath, vbDirectory) = "" Then MkDir folderPath folderPath = folderPath & "\" & Format(dateCell.Value, "yyyy-mm-dd") If Dir(folderPath, vbDirectory) = "" Then MkDir folderPath folderPath = folderPath & "\" & city If Dir(folderPath, vbDirectory) = "" Then MkDir folderPath folderPath = folderPath & "\" & address If Dir(folderPath, vbDirectory) = "" Then MkDir folderPath Set stationBook = Workbooks.Add(xlWBATWorksheet) stash.Range("A53:I54").Copy With stationBook.Worksheets(1).Range("A1") .PasteSpecial Paste:=xlPasteValues .PasteSpecial Paste:=xlPasteFormats End With Application.CutCopyMode = False stationBook.SaveAs Filename:=folderPath & "\STATION.xlsx", FileFormat:=xlOpenXMLWorkbook MsgBox "Saved to " & folderPath & "\STATION.xlsx" End Sub Private Function FolderName(ByVal text As String) As String Dim character As Variant text = Trim(text) For Each character In Array("\", "/", ":", "*", "?", Chr(34), "<", ">", "|") text = Replace(text, character, "_") Next character Do While Len(text) > 0 If Right(text, 1) <> "." And Right(text, 1) <> " " Then Exit Do text = Left(text, Len(text) - 1) Loop FolderName = text End Function

3
Run the macro

Save the source workbook as a macro-enabled .xlsm file. In Excel, open Developer > Macros, select CreateStationWorkbook, and click Run. Select the date cell on FORMULAS when prompted.

4
Check the station workbook

Open the displayed Desktop\ASBUILT folder path and check STATION.xlsx. The STASH range should appear at A1:I2 with its values and formats.

Use VBA to create the folders and save the copied range
Folder names and existing files: This example uses the Windows Desktop path. Use valid Windows folder names, including avoiding reserved names such as CON. If STATION.xlsx already exists, Excel may ask whether to replace it.
Free Microsoft Office alternative

Work with station spreadsheets in WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open compatible Excel files and continue working with station data while migrating between office suites; it does not fix Excel VBA macro issues.

Open and edit station spreadsheet data in WPS SpreadsheetCompatible with Microsoft Excel .xlsx filesFamiliar spreadsheet interface for an easier transitionFree and lightweight office suite
QA img-9

Frequently Asked Questions

Which cell should I select for the date?

Select the cell containing the date on the FORMULAS sheet when the macro prompts you. The record does not specify a date cell, so the macro does not assume one.

Does the macro copy formulas from STASH?

No. PasteSpecial with xlPasteValues copies the displayed results rather than formulas, and xlPasteFormats copies the cell formatting.

What happens if the folders do not exist?

The macro checks each level under Desktop\ASBUILT and uses MkDir to create missing date, city, and address folders before calling SaveAs.

Why do some characters change in the folder names?

Windows does not allow characters such as slashes, colons, and question marks in folder names. The FolderName function replaces those characters with underscores.

Can I run this exact macro on a Mac?

Not as written. It uses WScript.Shell and Windows-style paths to locate the Desktop; a Mac version needs a different Desktop path and folder-creation approach.