Update a macro

Dim lngLastRow As Long

With Sheets("PASTE_NOTEPAD")
    lngLastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    .Rows(lngLastRow).Copy Destination:=Sheets("sheet1").Rows(lngLastRow)
End With

Select allOpen in new window

Dim lngLastRow As Long

With Sheets("PASTE_NOTEPAD")
    lngLastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    .Rows(lngLastRow).Copy Destination:=Sheets("sheet1").Rows("1")
End With

Select allOpen in new window

Sub MySub()

Dim lngLastRow As Long

With Sheets("PASTE_NOTEPAD")
    lngLastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    .Rows(lngLastRow).Copy Destination:=Sheets("sheet1").Rows(lngLastRow)
End With

End Sub

Select allOpen in new window

I verified that there is no data in A1100 to A5000

I need all of the data up to the last line from Paste_Notepad to get copied into Sheet1 starting at A1.

Sub MySub()
Dim lngLastRow As Long

With Sheets("PASTE_NOTEPAD")
    lngLastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    .Rows("1:" & lngLastRow).Copy Destination:=Sheets("sheet1").Rows("1")
End With
End Sub

Select allOpen in new window

Sub COPY_PASTE()
    With Sheets("PASTE_NOTEPAD")
        .Range("A1:A" & .Cells(.Rows.Count, 2).End(xlUp).Row).Copy Destination:=Sheets("sheet1").Range("A1")
    End With
End Sub

Select allOpen in new window

That can't be the case, there has to be something in column A.

nd you're sure you are running the updated macro, not the old 5000 row one?

Well, you could try this, it will use column B as the reference for used rows rather than A, just to see what it does.

It brought over the data, all 5001 rows

Sub MySub()
Dim lngLastRow As Long

With Sheets("PASTE_NOTEPAD")
    For lngLastRow = 5500 To 1 Step -1
        If Not IsEmpty(.Cells(lngLastRow, "A")) Then
            Exit For
        End If
    Next
    .Rows("1:" & lngLastRow).Copy Destination:=Sheets("sheet1").Rows("1")
End With
End Sub

Select allOpen in new window

Sub MySub()
Dim cel As Range

For Each cel In Sheets("PASTE_NOTEPAD").UsedRange.Cells
    cel = Replace(cel, Chr(147), "")
    cel = Replace(cel, Chr(148), "")
Next

End Sub

Select allOpen in new window

Sub Save_as_text_file()
Dim fPath As String, fname As String
Dim ws As Worksheet
Dim lngLastRow As Long

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set ws = ActiveWorkbook.ActiveSheet
With ws
    For lngLastRow = 5500 To 1 Step -1
        If Not IsEmpty(.Cells(lngLastRow, "A")) Then
            Exit For
        End If
    Next
    .Rows("1:" & lngLastRow).Copy Destination:=Sheets("sheet1").Rows("1")
End With
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

Select allOpen in new window

I have no idea what your worksheet looks like, not even the headers, at this point, but what you might consider is adding an addition column to the right of your entries, and using string functions to concatenate and format the various columns you have entered into the useful "line" that you want output to a file.  Then just save this "calculated" column to the file, rather than letting Excel save the various columns and do it's own formatting and delims, etc.

It's a technique I often use for building large BAT scripts, or building test data, etc.