Copy data in a range into a new blank notepad

[SteynBS]

Good day guys,

I need to copy and paste data from a range into a new blank notepad. I can get the notepad open and copy the data, but for some reason, it does not paste the data in the notepad. Any advice, please?

The range is in sheet "listing" from Columns A and B the range has to be dynamic as the range changes. I have to copy from Column A to C to the end of the last row based in Column A. The code I have sofar is:

Sub Open_NotePad()
Dim myApp As String

myApp = Shell("Notepad", vbNormalFocus)


With Sheets("Listing").Select
Range("A1:C1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
End With

SendKeys "^V"

SendKeys "^{HOME}"

Application.Goto Worksheets("SAP Listing").Range("A1")
End Sub

 

[SteynBS]

Try this version:

Sub MyCreateTextFile()

    Dim lr As Long
    Dim wb As Workbook
    Dim fName As String
    Dim fileSaveName As String
   
'   Capture active file path and build default file path and name
    fName = ActiveWorkbook.Path & "\Listing.txt"
   
'   Go to "Listing" Sheet
    Sheets("Listing").Activate
   
'   Find last row with data in column A
    lr = Cells(Rows.Count, "A").End(xlUp).Row
   
'   Copy columns A-C
    Range("A1:C" & lr).Copy
   
'   Insert new workbook
    Set wb = Workbooks.Add
   
'   Paste copied data
    Range("A1").Activate
    ActiveSheet.Paste
    Application.CutCopyMode = False
   
'   Prompt for file save name
    fileSaveName = Application.GetSaveAsFilename(fName, _
        fileFilter:="Text (Tab delimited)(*.txt), *.txt")
   
'   Save as text file
    If Right(fileSaveName, 4) = ".txt" Then
        ActiveWorkbook.SaveAs Filename:=fileSaveName _
            , FileFormat:=xlText, CreateBackup:=False
    Else
        MsgBox "You have not chosen a valid file name ending in .txt", vbOKOnly, "File Name Error!"
    End If
       
'   Close text file
    wb.Close
   
End Sub

Thank you, this works perfectly. Thank you very very much for your assistance.

 

[Joe4]

You are welcome.
Glad I was able to help!