copy/paste special between sheets

Hello everyone,

Newbie question.
i would like tot copie and paste between sheets (in the same workbook)
example: in sheet3 i use ='sheet1'!a1 , but with this method i don't get any (needed) text or background color?
i need that option because color can be changed in sheet1 and therefor the color in sheet3 must follow automatically
what to do ?? thanks

thanks, but a copie and paste special doesn't work for me, because i copy a formula (example: in sheet1 i copy cell L41 equal to =AANTALARG(L6:L38)*L3)
when i paste special in sheet3 i get =AANTALARG(#VERW!)*#VERW!
that's why i reverted to ='sheet1'!L41 with the known problems

thanks for not giving up on me.

sorry, i'm not the brightest bulb in the shop,
and i don't know how to attach an 'attachment'. i seem not to be permitted?
if i start a new threat to attach, i get the 'attach' icon but i can't browse for the desired file on my computer.

what is next?
thanks

thanks. in the last sheet = 'totalen' i wrote some comments for you to work with

I'm not sure if I'm following the original question correctly, but I read it that you want a formula which can copy the formatting of the source cell.

There is no formula that can do that, even vba is unreliable if you want it automated, changing formats doesn't trigger recalculation, so there is nothing to detect the changes made.

Hello, the sheet = 'totalen' is only for control/overview/totals. i do no work here.
the sheets = 'jan' , 'feb' etc are the sheets where the data and formats get changed
what i am trying to get is: the data and formats in 'totalen' must follow (automatically) what is changed in the other ones

if i read the comment of jason.b75 i'm affraid that i'm asking something that is not possible even with a simple vba/macro routine

Hello, the sheet = 'totalen' is only for control/overview/totals. i do no work here.
the sheets = 'jan' , 'feb' etc are the sheets where the data and formats get changed
what i am trying to get is: the data and formats in 'totalen' must follow (automatically) what is changed in the other ones

if i read the comment of jason.b75 i'm affraid that i'm asking something that is not possible even with a simple vba/macro routine

Now making a little more sense, it could be done with vba using a worksheet activate event, set up so that the formats are copied to the 'totalen' sheet when it is activated (logical thought is that if you're not looking at the sheet then the formats are not needed).

You might need to be a little more consistent to avoid overcomplicating things though. I don't know that there is a way to easily identify the parts of the format that have been changed, so it would probably mean copying all aspects of the formatting.

Jan and Feb sheets are formatted as number, totalen sheet is formatted as currency. Copying the format to totalen would remove the € symbol from the totalen sheet.

Jan and Feb sheets are formatted with a size 10 font, totalen is formatted with a size 11 font. Copying the format would reduce the font on totalen to size 10.

Any thoughts, Alan?

As it appears that only the font colour is needed, try something like this in the 'totalen' worksheet module.

Please Login or Register  to view this content.

The code is meant to detect any cell with a simple formula in the format of ='sheetname'!A1 which you appear to have in the relevant cells.

I haven't done anything to trap errors though, so it will be easy to break. Just throwing it in as a line of thought.

The 2 Replace commands could be nested, but sucuri was rejecting the post as an sql injection

thank you everyone!
i conclude that if i use the following formula in sheet3: =sheet1!A1 it only returs the value WITHOUT any format(s)
pitty!
i will close this thread, thanks again

The code i provide was based on your sample workbook and works fine with that.

It was deliberately set to be selective with the formats that it copies due to inconsistencies, of which the actual requirement remains unclarified (see my comments in post #12).

thank you very much. it does what you said it would do.
now i have to play with it to understand how it really works.