Find number in Row 1, Copy Paste Values in Rows below
 

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.

Find number in Row 1, Copy Paste Values in Rows below
 

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.

Find number in Row 1, Copy Paste Values in Rows below
 

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.

.

Find number in Row 1, Copy Paste Values in Rows below
 

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.

.

Find number in Row 1, Copy Paste Values in Rows below
 

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.

.

Find number in Row 1, Copy Paste Values in Rows below
 

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.

.