Save Notepad through VBA Macro, having Copied Range of Cells, Pasted into notepad

My current macro, copies range of rows from a sheet, and puts it into Notepad.

Sub NoteTxt ()
    Sheets("Sheet1").Select
    Range("S1:S200").Copy
    'Start Notepad And let it recieve focus
    Shell "notepad.exe", vbNormalFocus
    'Send the keys CTRL+V To Notepad (i.e the window that has focus)
    SendKeys "^V"
End Sub

I seek help to save the notepad file to a specific location on Drive, say C:\ABC\Note1.txt

Each time, the notepad file would be overwritten.

Thanks in Advance.

Sub GetInNotepad()
Dim wNew As Worksheet, sPath As String

sPath = "C:\ABC\Note1.txt"
Set wNew = ThisWorkbook.Sheets.Add

Application.ScreenUpdating = False

    Sheets("Sheet1").Range("S1:S200").Copy Range("A1")
    
    Application.DisplayAlerts = False
        wNew.SaveAs Filename:=sPath, FileFormat:=xlTextWindows
        wNew.Delete
    Application.DisplayAlerts = True
    
Application.ScreenUpdating = True

MsgBox "File Saved in " & sPath, vbInformation, "Task Completed"

End Sub

Thanks, Sixthsense,

this worked well, but few emendations required for following reason

Range S2:S200 value is derived using formula,

so, in Notepad (Output file) all line for S2:S200, appears as #REF!

Cell S1 (Header) has static text value , and output (1st line in notepad) albeit comes properly, it is inside inverted comma, at the beginning, and end, how to avoid such " "

MOREOVER, I've about some 4-5 sheets in a workbook, and I will use this macro for each sheet to store its range value in different Text (NOTEPAD) Files.

Replace this line

  Sheets("Sheet1").Range("S1:S200").Copy Range("A1")

With

    Sheets("Sheet1").Range("S1:S200").Copy
    Range("A1").PasteSpecial xlPasteValues

For me the exported notepad content is not adding any Double Quotes

this time, perfect, but now each rows starts, and ends with Inverted comma, ( " )


My is Excel 2003

Moreover, entire Workbook, looks like saved as Sheet1.txt file, so when i save macro or workbook, it prompts for this changes

What is your windows Version? Since I tested it in Windows XP and I am not getting any double quotes

Originally Posted by analystbank

Moreover, entire Workbook, looks like saved as Sheet1.txt file, so when i save macro or workbook, it prompts for this changes

Not sure what you are trying to say with the above line since the macro is not written in that way...

Try this final version of code

Sub GetInNotepad()
Dim wNew As Worksheet, sPath As String, wTemp As Workbook

sPath = "C:\ABC\Note1.txt"
Set wNew = ThisWorkbook.Sheets.Add

Application.ScreenUpdating = False

    Sheets("Sheet1").Range("S1:S200").Copy
    Range("A1").PasteSpecial xlPasteValues
    
    Application.DisplayAlerts = False
        wNew.Move
        With ActiveWorkbook
            .SaveAs Filename:=sPath, FileFormat:=xlText
            .Close False
        End With
    Application.DisplayAlerts = True
    
Application.ScreenUpdating = True

MsgBox "File Saved in " & sPath, vbInformation, "Task Completed"

End Sub

Change the file format from

FileFormat:=xlText

to

FileFormat:=xlUnicodeText

modified, per your suggest to FileFormat:=xlUnicodeText,


but still same error 1004....

Sample file is attached. Currently working on sheet M

Thanks a ton. @YAsser,

Wonderful. Done

A suitable modification, in line

Set Rng = Sheets("M").Range("S1:S200")

like Set Rng = Sheets("M").Rang.Selection.End(xlDown)).Select

in order to ensure, that whatever is the end, instead of S200, only that much would be taken from S1