MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Copy + pates VBA


Posted by Jack in UK on January 06, 2002 12:10 PM

Jo , i need spme code that will paste as velue but if is number needs to be right aligned and if test needs to be lefy aligned, else of cause formulas will not see the data

any ideas guys??
Thank for time on this one, someone told me paste special add, but need to copy 0 from different cell, this works but cant code it out...

Cheers Jack


Posted by Tom Urtis on January 06, 2002 6:00 PM

Hi Jack, Happy New Year, a couple questions & thoughts:

Do you want the source data (that which is being copied) to be formatted on the spot, or just copy it to another range and then format it after paste special value?

If you want the source (copy from) range formatted first, what about conditional formulas in that range that might return a number or text depending on condition?

As a suggestion, might this work (not actual code, just loose typing here):

For Each Cell in Selection
SpecialCells(Text) format this way
Else format that way

What do you think?

Tom Urtis

Posted by Jack in UK on January 07, 2002 10:53 AM

Hi Tom
First Happy New Year also, bad news in US from Uk news, i dont know, all sad really, i just want people to help and get on.

Ok i will convert data (format) to read x but neet to be true on the fly so function or code to return lable left and number right, then paste as value.

My formula could be anywhare as my new work is lookup on 3 to 4 SS?? so needs to find result and copy over then paste as true value as you say, but it wont ie text is OK but numbers are returned as lables ie left aligned not right as should be so next look up fails, and i have to convert by add column and convert, paste as value and delete original column, drive me nuts..

Hope ive said enough, paste add work if i have copied 0 (zero) and use paste specila ADD then will converty, but will not work in VBA???

So im stuck...

Cheers Tom
Rdgs Jack