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.

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.

Get the position of the nth using excel vba1

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

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

export each sheet to csv2

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)
    'This temporarily adds a sheet named "Test."
    Sheets.Add.Name = "Test"
    '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
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    'Indicate save action.
    MsgBox "Text File: " & myFolder & "Saved!"
    'Go to top of sheet.
End Sub

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

save range selection to text file2

Step6: Select one range to be copied. Click OK button.

save range selection to text file3

Step7: enter one filename in File name text box in the Save As dialog box. Click Save button.

save range selection to text file4

Step8: open the newly created text file test1112.txt in your local disk.

save range selection to text file5

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

This video will demonstrate how to use VBA code to save a range selection to a text file in Excel.

Leave a Reply