How to Copy formats and formulas in vba?

[DK]

The following statement copies the formulas in one range to another range of
cells.
How can I also copy the formats of "ExpRow"?
Thank you.

....
r = Range("MtrCounter").Value
c = Range("ExpRow").Columns.Count
With Sheet2
.Range(.Cells(1, 1), .Cells(r, c)) = Range("ExpRow").Formula
End With
....

 

[Rick Rothstein]

This will probably work...

r = Range("MtrCounter").Value
c = Range("ExpRow").Columns.Count
With Sheet2
.Range("ExpRow").Copy .Range(.Cells(1, 1), .Cells(r, c))
.Range(.Cells(1, 1), .Cells(r, c)) = Range("ExpRow").Formula
End With

 

[DK]

I found a solution. However, if you can see a better way I'd be glad to see
it.
You guys are so good the answers arrive before you send them.
Thanks again for all you do.
DK

With Sheet2
.Range(.Cells(1, 1), .Cells(r, c)) = Range("ExpRow").Formula
.Range("ExpRow").EntireRow.Copy
.Range(.Cells(1, 1), .Cells(r, c)).PasteSpecial Paste:=xlPasteFormats, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With

 

[DK]

Hey! I didn't know that one could copy from > to in that manner.
Just the one line within the With statment seems to do the entire job.

.Range("ExpRow").Copy .Range(.Cells(1, 1), .Cells(r, c))

Thanks very much Rick.
DK

 

[Rick Rothstein]

That single line copies the formula, but it puts the same formula in each
cell (all cell references are the same, they don't change in the same way as
when you "copy a formula down")... you need the second line I posted to do
that (it overwrites the "static" formulas with the "copy down" type of
formulas)... I only used the Copy statement to quickly move the format into
the cells where you wanted the formulas.

 

[DK]

Rick,
I don't know if it makes any difference that I'm using xl-2007.
I checked the resulting formulas using just the one line of code and it
seems to be ok.
However I may keep the .Formula line just to make sure.
Here is a small sample using only .Range("ExpRow").Copy .Range(.Cells(1, 1),
..Cells(r, c)).
1st row is part of "ExpRow" next rows follow below.
The first formula is common to all records, the relative formulas refer to
the list of records.
= testby =Input!D16 =Input!E16

= testby =Input!D17 =Input!E17

= testby =Input!D18 =Input!E18

Thanks again.

 

[Rick Rothstein]

I'm using XL2003 and what I explained was necessary to make it work on my
system.