How to Copy formats and formulas in vba?
 

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
....

How to Copy formats and formulas in vba?
 

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

--
Rick (MVP - Excel)


"DK" wrote in message
...
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
...

How to Copy formats and formulas in vba?
 

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" wrote in message
...
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
...

How to Copy formats and formulas in vba?
 

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" wrote in message
...
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

--
Rick (MVP - Excel)


"DK" wrote in message
...
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
...

How to Copy formats and formulas in vba?
 

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.

--
Rick (MVP - Excel)


"DK" wrote in message
...
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" wrote in message
...
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

--
Rick (MVP - Excel)


"DK" wrote in message
...
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
...

How to Copy formats and formulas in vba?
 

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" wrote in message
...
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.

--
Rick (MVP - Excel)


"DK" wrote in message
...
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" wrote in message
...
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

--
Rick (MVP - Excel)


"DK" wrote in message
...
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
...

How to Copy formats and formulas in vba?
 

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

--
Rick (MVP - Excel)


"DK" wrote in message
...
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" wrote in message
...
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.

--
Rick (MVP - Excel)


"DK" wrote in message
...
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" wrote in message
...
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

--
Rick (MVP - Excel)


"DK" wrote in message
...
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
...