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.

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

With

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

Change the file format from

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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