VBA & Macro Problems

Export an Excel VBA Range and Filename Based on a Cell Value

Adam DavisAdam Davis Nov 18, 2026 869 views

Question details

Export one of two worksheet ranges to a text file, with the range and filename determined by FORMULAS!B11.

Export an Excel VBA Range and Filename Based on a Cell Value
Product
Microsoft Excel VBA
Device & OS
not provided
Scenario
A macro needs to choose an export range and text filename according to a code in the FORMULAS worksheet.
Observed behavior
Codes L101, L103, L111, L201, L203, and L211 should export A46:C60 to SL.txt; L701 and L703 should export H46:J60 to NET.txt.
Before you start

Save the workbook as a macro-enabled Excel workbook in a local folder and confirm that the FORMULAS sheet contains the code in B11. Identify the sheet containing the export ranges; the example uses FORMULAS, but you can change the source sheet in the code.

Solution 1Recommended

Use Select Case to export the matching range and filename

Choose the range and text filename from FORMULAS!B11, then save the values through a temporary workbook.

The macro exports cell values rather than formulas or formatting. The text file is saved in the same folder as the macro workbook, using Excel's tab-delimited text format.

1
Open the VBA editor

In Excel, open the workbook and select Developer > Visual Basic. In the VBA editor, select Insert > Module.

2
Add the export macro

Paste the following code into the module. If the export ranges are on another sheet, change Worksheets("FORMULAS") on the Set sourceSheet line to that sheet's name; leave the B11 reference unchanged. Sub ExportRangeByCode() Dim sourceSheet As Worksheet Dim sourceRange As Range Dim tempBook As Workbook Dim code As String Dim outputName As String Dim errorMessage As String If Len(ThisWorkbook.Path) = 0 Then MsgBox "Save this workbook in a local folder first." Exit Sub End If On Error GoTo ExportError Set sourceSheet = ThisWorkbook.Worksheets("FORMULAS") code = UCase$(Trim$(CStr(ThisWorkbook.Worksheets("FORMULAS").Range("B11").Value))) Select Case code Case "L101", "L103", "L111", "L201", "L203", "L211" Set sourceRange = sourceSheet.Range("A46:C60") outputName = "SL.txt" Case "L701", "L703" Set sourceRange = sourceSheet.Range("H46:J60") outputName = "NET.txt" Case Else MsgBox "No export is configured for code: " & code Exit Sub End Select Set tempBook = Workbooks.Add(xlWBATWorksheet) tempBook.Worksheets(1).Range("A1").Resize(sourceRange.Rows.Count, sourceRange.Columns.Count).Value = sourceRange.Value tempBook.SaveAs Filename:=ThisWorkbook.Path & Application.PathSeparator & outputName, FileFormat:=xlText tempBook.Close SaveChanges:=False MsgBox "Export saved as " & outputName Exit Sub ExportError: errorMessage = Err.Description On Error Resume Next If Not tempBook Is Nothing Then tempBook.Close SaveChanges:=False On Error GoTo 0 MsgBox "Export failed: " & errorMessage End Sub

3
Run and check the export

Return to Excel, select Developer > Macros, choose ExportRangeByCode, and select Run. Open the workbook's folder and check SL.txt or NET.txt. Respond to any Excel prompt if a file with the same name already exists.

Use Select Case to export the matching range and filename
Unlisted codes: If B11 contains an unlisted code, the macro displays a message and does not create an export file.
Free Microsoft Office alternative

Work with Excel files in WPS Spreadsheets

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and compatibility with Excel formats, making it easy to move everyday workbook tasks to WPS. Excel VBA macros may require separate compatibility testing and are not fixed by switching applications.

Review and edit worksheet ranges in WPS SpreadsheetsOpen and save common Microsoft Excel formats, including XLSXUse a familiar interface for everyday spreadsheet workGet a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Which B11 values export to SL.txt?

L101, L103, L111, L201, L203, and L211 select A46:C60 and the filename SL.txt. The macro reads B11 from the FORMULAS sheet.

What happens if B11 contains an unlisted code?

The Select Case statement reaches Case Else, displays a message, and exits without creating a text file. Add another Case in the VBA module if you need to support more codes.

How do I export a range from a different worksheet?

In the VBA editor, change the sheet name on the Set sourceSheet line. Keep the FORMULAS reference on the code line if B11 remains on that sheet.

Does the text file contain formulas or formatting?

No. The macro assigns the source range's values to a temporary workbook and saves it as tab-delimited text. Formulas and worksheet formatting are not preserved.

Where is the exported file saved?

The macro saves SL.txt or NET.txt in the same folder as the workbook containing the macro. Save the workbook in a local folder before running it.