Copy data in a range into a new blank notepad

SteynBS

Board Regular
Joined
Jun 27, 2022
Messages
97
Office Version
  1. 365
Platform
  1. Windows
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
 
Try this version:
VBA Code:
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.
 
Upvote 0

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
You are welcome.
Glad I was able to help!
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top