The values are fairly straight forward. Something like this...

With Sheets("2")
Sheets("1").Range(.UsedRange.Address).Value = .UsedRange.Value
End With

The formats however need to be copied and pasted so the above code doesn't
do you a lot of good.
--
HTH...

Jim Thomlinson


"jerm" wrote:

Hi,
I am trying to copy the formats and values from one sheet to another.
I want to get around using the copy paste method so i am trying
something like this:

Sheets("1").Range("A1:AA160") = Sheets("2").Range("A1:AA160").Values

Ok, that works fine and dandy... but what if the range is dynamic!

I want something like this
Sheets("1").UsedRange = Sheets("2").UsedRange.Values

but vba doesnt like that

I also want to do the same thing for the formats like this
Sheets("1").UsedRange.FormatConditions =
Sheets("2").UsedRange.FormatConditions

No dice either...

Could anyone steer me in the right direction.
thanks
jer