I need to update the following macro to trim out any open or closed quotation marks. When I am running this I am getting some quotation marks thats being entered into the .txt file. Also, is there a way to remove any blank lines at the end of the sheet??? I dont need it exporting a bunch of spaces or lines...
Sub Save_as_text_file()
Dim fPath As String, fname As String
Dim ws As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set ws = ActiveWorkbook.ActiveSheet
fname = Application.ActiveWorkbook.Path & "\" & _
ThisWorkbook.Worksheets("BOB_BUILDER").Range("A2") & "_" & _
ws.Name & ".txt"
fPath = Application.ActiveWorkbook.Path
ws.Copy
ActiveWorkbook.SaveAs fname, xlUnicodeText
Application.ActiveWorkbook.Close
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Or maybe modifying this would be easier? I set the range to copy from 1:5000. Just really needs to copy the last line of data from "PASTE_NOTEPAD"
Sub COPY_PASTE()
Sheets("PASTE_NOTEPAD").Range("A1:A5000").Copy Destination:=Sheets("sheet1").Range("A1:A5000")
End Sub