make a cell the paste value of another

Hi all

I'm having a random moment that I think I've simply thought to much about and can't see the answer.

How do you make a cell, say G1 for example in sheet1 the copy paste value of the formula A1 in sheet 2.

If I just use =Sheet2!A1 it simply copies the formula value which causes a circula reference issue.

So what would go into G1 to grab the value only and not the formula?

Cheers

if formula in sheet2!a1 references sheet1!g1 it will be circular.
what's the formula in sheet!2a1 ?

Doh, of course it does LOL, and yes it does, oops.

Ok from a different approach.

Please Login or Register  to view this content.

Sheet 2 updates dependant on the values of sheet 1.

If I use the =B2 then yes it creates a circular reference which I missed, but if I use copy/paste value from sheet 2 into sheet 1 then it works fine and sheet 2 updates from the new values in sheet 1.

What I'm try to achieve is the code above visually switches sheets, highlights blocks switches back etc,.... which when the macro is run in a loop there is this almight screen flickering going on which, although I get the result I'm after, I don't want the mass flickering from visual movements.

Does that makes sense?

Thanks all, much appreciate your help, works great

That definately comes under, cor I wish I new that sooner.