Help copying cell formula ?

  • Thread starter Thread starter NickTheBatMan
  • Start date Start date
N

NickTheBatMan

I have a worksheet to which I'm trying to associate data in cells on
other sheets.

The data is being put into cells A5-A10, B5-B10 though to I 10 in the
end - that's 54 cells on 27 sheets to populate the D column in this
'Headers' sheet in the book.

I know that if I put = into column D then go to the cell on the
relevant sheet and press enter that it will automatically populate the
cell with the right formula, this I can then copy sequentially as far
as I want - in this case 6 cells down.

What I want to do of course is to start at D7 with the cell B5 of the
same sheet going through to B10 then C5 to C10 etc.
I can't work out an easy way of doing this :( The only way I can find
to do this is to go into the formula in cell D7 and type in B5 then
extend that down then C5 etc... and this is taking ages :(

I'm hoping there's an easy way to do this ?!?!?

Nick
 
Use a macro. Select the sheet where you want the links to appear (remove everything from column D
except the header) and the run the macro below.

HTH,
Bernie
MS Excel MVP

Sub TryNow()
Dim myA As String
Dim myS As Worksheet
Dim myD As Worksheet
Dim i As Integer
Dim j As Integer

myA = "A5:I10"
Set myD = ActiveSheet

For Each myS In ActiveWorkbook.Worksheets
If myS.Name <> myD.Name Then
For i = Range(myA).Cells(1).Column To Range(myA).Cells(Range(myA).Cells.Count).Column
For j = Range(myA).Cells(1).Row To Range(myA).Cells(Range(myA).Cells.Count).Row
myD.Cells(Rows.Count, 4).End(xlUp)(2).Formula = _
"='" & myS.Name & "'!" & Cells(j, i).Address
Next j
Next i
End If
Next myS

End Sub
 
Back
Top