How to Save a Range Selection to Text File in Excel

This post will guide you how to Copy a Range of Selection to a Text file in Excel. How do I copy/paste a range from the current worksheet to a notepad text file with VBA Macro in Excel 2013/2016/2019/365.

Assuming that you have a list of data in range B1:B5 in your worksheet, and you wish to copy this range to another worksheet or a notepad text file, and save it as a .txt or xlsx. You can copy the selection to the clipboard by manually, open up a Notebook file or what ever text application you use, or you can open an blank worksheet, then paste it in there and save as the file. This method can work well, but it is not a good way. You can use an Excel VBA Macro to accomplish it quickly. And this post will show you how to use an Excel VBA code to save a range of selection to a text file.

Table of Contents

1. Save a Range Selection as Text File
2. Video: Save a Range Selection to Text File in Excel

1. Save a Range Selection as Text File

You can use an Excel VBA macro to save the selection as a text file without copying and pasting to a new file. Just do the following steps:

Step1: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Step2: then the “Visual Basic Editor” window will appear.

Step3: click “Insert” ->”Module” to create a new module.

Step4: paste the below VBA code into the code window. Then clicking “Save” button.

Sub SaveSelectionAsTextFile()
    Dim myFolder As String
    'Save Range as Text File.
    Set myRange = Application.Selection
    Set myRange = Application.InputBox("Select one Range to be copied", "SaveSelectionAsTextFile", myRange.Address, Type:=8)
    ActiveSheet.Activate
    myRange.Copy
    'This temporarily adds a sheet named "Test."
    Sheets.Add.Name = "Test"
    Sheets("Test").Select
    ActiveSheet.Paste
    'Ask user for folder to save text file to.
    myFolder = Application.GetSaveAsFilename(fileFilter:="Text Files (*.txt), *.txt")
    'Save selected data as text file in users selected folder.
    ActiveWorkbook.SaveAs Filename:=myFolder, FileFormat:=xlText, CreateBackup:=False
    'Remove temporary sheet.
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    ActiveWindow.SelectedSheets.Delete
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    'Indicate save action.
    MsgBox "Text File: " & myFolder & "Saved!"
    'Go to top of sheet.
    Range("A1").Select
End Sub

Step5: back to the current worksheet, then run the above excel macro. Click Run button.