I'm looking for help with a macro that will allow me to find every column
(from D:W) with a number in row1 (any number) and will copy the formula on
row 2 to row 100 on that column.

For example:

if cell D1 = 3 then copy the formula on D2 and paste on D3:D100
(also, IF POSSIBLE, copy D3:D100 AGAIN and paste as values on the same range)

repeat for all column where row1 has a number


Any help would be appreciated.

Give this macro a try...

Sub CopyFormulas()
Dim Cell As Range
For Each Cell In Range("D1:W1").SpecialCells( _
xlCellTypeConstants, xlNumbers)
Cell.Offset(1).Copy
Cell.Offset(2).Resize(98).PasteSpecial xlPasteValues
Next
Application.CutCopyMode = False
ActiveCell.Select
End Sub

--
Rick (MVP - Excel)


"Alonso" wrote in message
...
I'm looking for help with a macro that will allow me to find every column
(from D:W) with a number in row1 (any number) and will copy the formula
on
row 2 to row 100 on that column.

For example:

if cell D1 = 3 then copy the formula on D2 and paste on D3:D100
(also, IF POSSIBLE, copy D3:D100 AGAIN and paste as values on the same
range)

repeat for all column where row1 has a number


Any help would be appreciated.

Thanks Rick
close, but not what I need
this one copy the value on row2 and paste it on row3-row100
I'm looking to copy the FORMULA on row2, paste it on row3-row100, THEN copy
the range row3-row100 and paste as values on the same range

hope is clearer now

again thanks for your help



"Rick Rothstein" wrote:

Give this macro a try...

Sub CopyFormulas()
Dim Cell As Range
For Each Cell In Range("D1:W1").SpecialCells( _
xlCellTypeConstants, xlNumbers)
Cell.Offset(1).Copy
Cell.Offset(2).Resize(98).PasteSpecial xlPasteValues
Next
Application.CutCopyMode = False
ActiveCell.Select
End Sub

--
Rick (MVP - Excel)


"Alonso" wrote in message
...
I'm looking for help with a macro that will allow me to find every column
(from D:W) with a number in row1 (any number) and will copy the formula
on
row 2 to row 100 on that column.

For example:

if cell D1 = 3 then copy the formula on D2 and paste on D3:D100
(also, IF POSSIBLE, copy D3:D100 AGAIN and paste as values on the same
range)

repeat for all column where row1 has a number


Any help would be appreciated.

.

Rick

I think I got it with your help

something like this seems to be working

Sub CopyFormulas()
Dim Cell As Range
For Each Cell In Range("g1:i1").SpecialCells( _
xlCellTypeConstants, xlNumbers)
Cell.Offset(1).Copy
Cell.Offset(2).Resize(98).PasteSpecial
Cell.Offset(2).Resize(98).Copy
Cell.Offset(2).Resize(98).PasteSpecial xlPasteValues
Next
Application.CutCopyMode = False
ActiveCell.Select
End Sub


"Rick Rothstein" wrote:

Give this macro a try...

Sub CopyFormulas()
Dim Cell As Range
For Each Cell In Range("D1:W1").SpecialCells( _
xlCellTypeConstants, xlNumbers)
Cell.Offset(1).Copy
Cell.Offset(2).Resize(98).PasteSpecial xlPasteValues
Next
Application.CutCopyMode = False
ActiveCell.Select
End Sub

--
Rick (MVP - Excel)


"Alonso" wrote in message
...
I'm looking for help with a macro that will allow me to find every column
(from D:W) with a number in row1 (any number) and will copy the formula
on
row 2 to row 100 on that column.

For example:

if cell D1 = 3 then copy the formula on D2 and paste on D3:D100
(also, IF POSSIBLE, copy D3:D100 AGAIN and paste as values on the same
range)

repeat for all column where row1 has a number


Any help would be appreciated.

.

Sorry, give this macro a try instead...

Sub CopyFormulas()
Dim Cell As Range
For Each Cell In Range("D1:W1").SpecialCells( _
xlCellTypeConstants, xlNumbers)
Cell.Offset(1).Copy Cell.Offset(2).Resize(98)
Cell.EntireColumn.Value = Cell.EntireColumn.Value
Next
End Sub

--
Rick (MVP - Excel)


"Alonso" wrote in message
...
Thanks Rick
close, but not what I need
this one copy the value on row2 and paste it on row3-row100
I'm looking to copy the FORMULA on row2, paste it on row3-row100, THEN
copy
the range row3-row100 and paste as values on the same range

hope is clearer now

again thanks for your help



"Rick Rothstein" wrote:

Give this macro a try...

Sub CopyFormulas()
Dim Cell As Range
For Each Cell In Range("D1:W1").SpecialCells( _
xlCellTypeConstants, xlNumbers)
Cell.Offset(1).Copy
Cell.Offset(2).Resize(98).PasteSpecial xlPasteValues
Next
Application.CutCopyMode = False
ActiveCell.Select
End Sub

--
Rick (MVP - Excel)


"Alonso" wrote in message
...
I'm looking for help with a macro that will allow me to find every
column
(from D:W) with a number in row1 (any number) and will copy the
formula
on
row 2 to row 100 on that column.

For example:

if cell D1 = 3 then copy the formula on D2 and paste on D3:D100
(also, IF POSSIBLE, copy D3:D100 AGAIN and paste as values on the same
range)

repeat for all column where row1 has a number


Any help would be appreciated.

.

This modification to my last posted macro should be more efficient for your
given conditions...

Sub CopyFormulas()
Dim Cell As Range
For Each Cell In Range("D1:W1").SpecialCells( _
xlCellTypeConstants, xlNumbers)
Cell.Offset(1).Copy Cell.Offset(2).Resize(98)
Cell.Resize(100).Value = Cell.Resize(100).Value
Next
End Sub

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Sorry, give this macro a try instead...

Sub CopyFormulas()
Dim Cell As Range
For Each Cell In Range("D1:W1").SpecialCells( _
xlCellTypeConstants, xlNumbers)
Cell.Offset(1).Copy Cell.Offset(2).Resize(98)
Cell.EntireColumn.Value = Cell.EntireColumn.Value
Next
End Sub

--
Rick (MVP - Excel)


"Alonso" wrote in message
...
Thanks Rick
close, but not what I need
this one copy the value on row2 and paste it on row3-row100
I'm looking to copy the FORMULA on row2, paste it on row3-row100, THEN
copy
the range row3-row100 and paste as values on the same range

hope is clearer now

again thanks for your help



"Rick Rothstein" wrote:

Give this macro a try...

Sub CopyFormulas()
Dim Cell As Range
For Each Cell In Range("D1:W1").SpecialCells( _
xlCellTypeConstants, xlNumbers)
Cell.Offset(1).Copy
Cell.Offset(2).Resize(98).PasteSpecial xlPasteValues
Next
Application.CutCopyMode = False
ActiveCell.Select
End Sub

--
Rick (MVP - Excel)


"Alonso" wrote in message
...
I'm looking for help with a macro that will allow me to find every
column
(from D:W) with a number in row1 (any number) and will copy the
formula
on
row 2 to row 100 on that column.

For example:

if cell D1 = 3 then copy the formula on D2 and paste on D3:D100
(also, IF POSSIBLE, copy D3:D100 AGAIN and paste as values on the same
range)

repeat for all column where row1 has a number


Any help would be appreciated.

.