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

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
> ...
>

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
> ...
>

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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
>> ...
>>
>

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> 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,
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> 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" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>> 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
>>>> ...
>>>>
>>>
>>
>>
>

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

--
Rick (MVP - Excel)


"DK" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> 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" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>> 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" <(E-Mail Removed)> wrote in message
>>>> news:(E-Mail Removed)...
>>>>> 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
>>>>> ...
>>>>>
>>>>
>>>
>>>
>>
>
>