optimized way for copying formats and values
 

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

optimized way for copying formats and values
 

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