Hi
I have a specific sheet that I would like to create a macro to save a copy of only this sheet as a txt file without removing it from the current workbook.
Multiple people use this workbook so txt file name and folder location to save will vary and need to be chosen by the user.
I was working from the code below but I got a Runtime Error at line 11. See photo.
I have a specific sheet that I would like to create a macro to save a copy of only this sheet as a txt file without removing it from the current workbook.
Multiple people use this workbook so txt file name and folder location to save will vary and need to be chosen by the user.
I was working from the code below but I got a Runtime Error at line 11. See photo.
VBA Code:
Sub SaveSheetAsTextFile()
Dim ws As Worksheet
Dim strFile As String
Dim strPath As String
Set ws = ThisWorkbook.Sheets("Trio Quant Import (2)") ' Replace "Sheet1" with the name of your sheet
strFile = Application.GetSaveAsFilename(FileFilter:="Text Files (*.txt), *.txt", Title:="Save As Text File") ' Open the Save As dialog box
If strFile <> "False" Then ' If the user clicked Save
strPath = Left(strFile, InStrRev(strFile, "\")) ' Extract the path from the file name
ws.Copy ' Copy the sheet to a new workbook
ActiveWorkbook.SaveAs Filename:=strPath & Application.PathSeparator & Mid(strFile, InStrRev(strFile, "\") + 1), FileFormat:=xlTextWindows ' Save the new workbook as a text file with the user-specified name and location
ActiveWorkbook.Close SaveChanges:=False ' Close the new workbook without saving changes
End If
End Sub