Export an Excel VBA Range and Filename Based on a Cell Value
Question details
Export one of two worksheet ranges to a text file, with the range and filename determined by FORMULAS!B11.

- 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.
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.
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.
In Excel, open the workbook and select Developer > Visual Basic. In the VBA editor, select Insert > Module.
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
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.

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.

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.




