Excel Macro for Autofill Formula for Linking 2 Different Worksheets 1

[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

 

[PHV]

Replace this:
ActiveCell.FormulaR1C1 = "='[" & strFileName & ".xls]Sheet1'!$A$" & i
with this:
ActiveSheet.Cells(i, 1).Formula = "='[" & strFileName & ".xls]Sheet1'!$A$" & i

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Marryp]

It works.....thank you so much!