Copy to Clipboard, concatenate, paste

This one really has two different problems. In my Personal.xls I'm using a Concatenate to Clipboard macro I copied from the internet some time ago, and want to change two key aspects of it. The full code I'm using is posted at the bottom of this post, but here's the two problems:

1) The current macro uses this code to choose the selection to copy:

Please Login or Register  to view this content.

I have the macro setup as a button, and I WANT to first select my range to copy by highlighting the cells, then click the button to copy. However, what happens is I click the button, get a popup telling me to select the range, move the box out of my way, select my range, and click OK to make it copy. WAY too labor intensive! So I just need to "Set rngToConcat" equal to something that means "Area Highlighted". I can't think of what code I need to put in.

2) The second problem is a bit trickier, and I'm not sure it can be done. I frequently encounter cells which display as something like "0014", but whose value is actually "14". So, if I have 5 of these cells in a column, looking something like this:

Please Login or Register  to view this content.

When I run my macro and paste elsewhere it looks like this"

Please Login or Register  to view this content.

I'd like to copy, concatenate and paste keeping the formatting. Is this possible?

I'd appreciate any help you can provide on this. Here's the full code for the macro:

Please Login or Register  to view this content.

JB,

It's so good to hear from you!

Your solution to 1 was just what I needed, i just couldn't think of it, so thank you very much.

Your solution to 2 won't work for me, as it's too specific (that's my fault, not yours). I want to able to "concatenate to clipboard" all the time, because it's really handy in my work. When I said "something like "0014"" I should have been more explicit: sometimes it's formatted as "0014", sometimes it's "00014", sometimes it's "0000-14", etc. So, is there a way to preserve the formatting however it's formatted, not only for one particular format?

Yes! You Rock!

This macro was handy before, but its definitely a keeper now, thanks to you. I tried it with three differently formatted sets of numbers, and it worked flawlessy each time. I love it!

Now, if you want to handle another challenge, I haven't come to this situation yet but I'm sure I will, if I have cells with different font color formats, or italics, etc, could we preserve that formatting as well? I tested with color and that's not working yet. It's not critical, but it'd be nice to have the ability. What do you think?

Hmm, I have this macro which will concatenate and preserve the color formats. I had hoped that somehow I could join the two together, to get an all-inclusive concatenate button, but maybe I'll just set them up as two different macros.

Please Login or Register  to view this content.