Excel Formula for Linking 2 Different Worksheet

[Marryp]

Hello

I need help to write a macro to be able to automate linking of 2 worksheets. There will be master worksheet (file) with different sheets where each sheet is link to different files. Each cell in column A per sheet is linked.

Example:
File A with Sheet Entry
Master File with Sheet Staff1

Master File - Staff
A2 ='[File A.xls]Entry!$A$2
A3 ='[File A.xls]Entry!$A$3
A4 ='[File A.xls]Entry!$A$4
A5 ='[File A.xls]Entry!$A$5
and so on

I need a way to automate this process via a macro. I am thinking of prompting the user to specify the filename since there will be several files to link depending on the sheet and the last row to link the cell. Since we know that the cell will always be the same as the cell we are linking.

I have something like this but not quite right...

Sub LinkStaffEntry()

Dim i As Integer
Dim intRowCount As Integer
Dim strFileName As String

strFileName = Application.InputBox("Please enter the filename")
intRowCount = Application.InputBox("Please enter the last row to link")

For i = 2 To intRowCount

MsgBox "='[" & strFileName & ".xls]Sheet1'!$A$" & i
ActiveCell.FormulaR1C1 = "='[" & strFileName & ".xls]Sheet1'!$A$" & i
'ActiveCell.Offset(1, 0).Select

Next i

End Sub

 

[SkipVought]

Marryp,

Please post VBA macro questions in forum707.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Marryp]

Ok Thanks!