Copy/paste and add _ to values

**jagerstal** · 04-06-2012, 08:23 AM

Hi,

I need a fast macro that will copy values from column A, paste those values in column A on Sheet1, then proceed to column B and paste those values to column A on sheet1, and so forth to column DZ on the original sheet.

I also want to add _ at the end of every value. Currently I have a macro for adding _:

Please Login or Register  to view this content.

But the code is way to unefficient. I've attached a sample file which may better explain what I'm trying to achieve:
Sample.xlsm

Thanks in advance

**jraj1106** · 04-06-2012, 08:57 AM

Hi
I have the data copied into sheet4. The output is in sheet1 H column.
Pls try sub named "test":

Sample copy.xlsm

Regards..

**Palmetto** · 04-06-2012, 09:19 AM

Please Login or Register  to view this content.

**davesexcel** · 04-06-2012, 09:48 AM

Try this

Please Login or Register  to view this content.

**jagerstal** · 04-06-2012, 09:59 AM

Thanks for your help. I ended up using the one by Dave as it seemed to be the fastest one.

**Palmetto** · 04-06-2012, 10:18 AM

I doubt there's much speed difference (more perception than reality) as my code and Dave's is nearly identical.
However, this line of Dave's code:

Please Login or Register  to view this content.

is more specific to users of versions prior to Excel 2007+ because those versions are limited to 65, 536 rows.
Seeing as you are using Excel 2010 and have *MANY* more rows, there is risk of truncating the data if the total number of copied cells in your worksheet exceeds 65,536. Dave's code will not see available rows beyond that number.

I am using the more generic "Cells" method, which is compatible with any version.