Help copying cell formula ?

[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

 

[Bernie Deitrick]

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