Copy a Range and Build a Folder Path with an Excel VBA Macro
Question details
Update an Excel VBA macro to copy a range and save a station workbook in folders based on worksheet values.

- 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.
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.
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.
In Excel, open the source workbook, press Alt+F11, and choose Insert > Module.
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
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.
Open the displayed Desktop\ASBUILT folder path and check STATION.xlsx. The STASH range should appear at A1:I2 with its values and formats.

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.

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.




