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

[Alonso]

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 D3100
(also, IF POSSIBLE, copy D3100 AGAIN and paste as values on the same range)

repeat for all column where row1 has a number


Any help would be appreciated.

 

[Rick Rothstein]

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

 

[Alonso]

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

 

[Alonso]

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]

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 Rothstein]

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