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. 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.
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.
[vb] Sub SaveSelectionAsTextFile() Dim myFolder As String 'By Joe Was. '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 [/vb]
Step5: back to the current worksheet, then run the above excel macro. Click Run button.
Step6: Select one range to be copied. Click OK button.
Step7: enter one filename in File name text box in the Save As dialog box. Click Save button.
Step8: open the newly created text file test1112.txt in your local disk.