Macro To Copy Cells as CSV

[kvanb796]

I have a very basic macro that currently just copies cells. The information is going to be pasted into a form online. The problem is the form does not retain tabs, and when it is pasted into excel it puts it all in the same row. It would be possible to put the information is as comma separated values, however.

Is there a way to add something to the macro to have it export as csv that can be pasted into notepad rather than a tab-separated?

Please Login or Register  to view this content.

Thanks!



EDIT:
I found this macro on another site and it copies the information as csv but when i paste it from notepad back into excel it doesn't separate the columns. Is there a way to copy a csv format notepad document and have it look like the original?

Please Login or Register  to view this content.

[Andy Pope]

Please use code tags when posting code.

This will build a comma delimitered string and put the text on the clipboard.

Please Login or Register  to view this content.

The quickest way to get the reference is to add a userform and then delete it.

[kvanb796]

That works for copying it but when it is pasted back into excel it all falls back into one cell. Is there a way to paste it so that the columns and rows remain like the original?

[Andy Pope]

By default excel will only unravel the information if it is tab delimitered.
You can use Data > Text to columns. to split the data out. The settings will be remember until changed or the next excel session.

[kvanb796]

Thank you so much, you've been so much help. There is just one more question I have. When I use the example of the macro that I gave you it puts the information in a notepad file as a .csv, but there are line breaks for each row:

A1, B1, C1, D1
A2, B2, C2, D2
A3, B3, C3, D3

Yours copies to the clipboard but it does not create new lines for each row (see below) so when it is put back in excel each row from the original is put into one row of the new sheet.

A1, B1, C1, D1, A2, B2, C2, D2, A3, B3, C3, D3

Is there any way we could add new lines for each row in your version before copying it out, or to combine the two methods?

[kvanb796]

Are there any ideas? I'm sorry, but it's a little time-sensitive

[Kenneth Hobson]

We can easily add the vbcrlf codes to Andy's routine if you want that.

Here is one method that I have used. Add these to a new workbook and run from a blank Sheet1 for example.

Notice that I replaced the tabs with a space character. You can use a comma as I did in the second Sub t example. After this snippet, I will show how to create it as a comma separated file. The 3rd part will show how to poke the text file's data back into Excel.

Please Login or Register  to view this content.

Add the other Subs and Functions and this new Sub for the comma separated example text file.

Please Login or Register  to view this content.

Now, we go to another sheet and play this code to see if we get the data from Fruit.txt inserted properly. You can put all of these Functions into their own Module.

Please Login or Register  to view this content.

[Andy Pope]

I would modify my code like this to get new lines as well as comma delimiters

Please Login or Register  to view this content.