Avatar of wrt1mea

asked on 

Update a macro

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
ActiveWorkbook.SaveAs fname, xlUnicodeText
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"

    Sheets("PASTE_NOTEPAD").Range("A1:A5000").Copy Destination:=Sheets("sheet1").Range("A1:A5000")
End Sub
Microsoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Martin Liss

8/22/2022 - Mon