John,

You can look through the cells with formulas, copying each block of cells.
See the code below.

HTH,
Bernie
MS Excel MVP

Sub CopyRow2FormulasDownToMatchColumnB()
Dim myArea As Range
Dim myRow As Long

myRow = Range("B65536").End(xlUp).Row

For Each myArea In Range("2:2"). _
SpecialCells(xlCellTypeFormulas).Areas
myArea.Copy myArea.Resize(myRow - 1, 1)
Next myArea

End Sub



"John" wrote in message
...
I see many similar postings but haven't been able to apply their fixes to
my
problem. Here is hoping someone can help.

I have a file with heading info in row 1 and data entry starting in row 2.
Row 2 contains lookup formulas in cells A2, D2-F2, L2-X2 and AV2-AX2. A2
looks up a location number based on a unique identifier (location name)
manually input into B2, the other cells lookup based on the info in A2 (a
more reliable unique identifier).

Column B is where I input my initial data (location name). I need to copy
these formulas down as many rows as I have locations listed in column B.
For
instance, if I have 5 entries (unique identifiers listed in colum B, the
formulas in row 2 will copy down through row 6)

I prefer to have only the columns with formulas copied down, but at the
very
least will be happy if column B is not overwrited because it contians my
list
of locations.

Any help would be appreciated.

Thanks

Hey - thanks Bernie It works perfect.

"Bernie Deitrick" wrote:

John,

You can look through the cells with formulas, copying each block of cells.
See the code below.

HTH,
Bernie
MS Excel MVP

Sub CopyRow2FormulasDownToMatchColumnB()
Dim myArea As Range
Dim myRow As Long

myRow = Range("B65536").End(xlUp).Row

For Each myArea In Range("2:2"). _
SpecialCells(xlCellTypeFormulas).Areas
myArea.Copy myArea.Resize(myRow - 1, 1)
Next myArea

End Sub



"John" wrote in message
...
I see many similar postings but haven't been able to apply their fixes to
my
problem. Here is hoping someone can help.

I have a file with heading info in row 1 and data entry starting in row 2.
Row 2 contains lookup formulas in cells A2, D2-F2, L2-X2 and AV2-AX2. A2
looks up a location number based on a unique identifier (location name)
manually input into B2, the other cells lookup based on the info in A2 (a
more reliable unique identifier).

Column B is where I input my initial data (location name). I need to copy
these formulas down as many rows as I have locations listed in column B.
For
instance, if I have 5 entries (unique identifiers listed in colum B, the
formulas in row 2 will copy down through row 6)

I prefer to have only the columns with formulas copied down, but at the
very
least will be happy if column B is not overwrited because it contians my
list
of locations.

Any help would be appreciated.

Thanks