troubleshooting Question

Update a macro

Avatar of wrt1mea
wrt1mea asked on
Microsoft ExcelMicrosoft Office
46 Comments2 Solutions149 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 2 Answers and 46 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 46 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros