Connect a cell dynamically with another sheet

[vc47dlc]

Dear all


I wrote following code


Sub Macro2()

Sheets("PRINT").Select




With ActiveSheet.UsedRange
lk = .Rows(.Rows.Count).Row
End With


For a = 1 To lk
san = Range("b" & a).Value


san2 = "'" & san & "'!" & Range("c" & a).Value
Range("e" & a).Select

ActiveCell.FormulaLocal = "=SUBSTITUTE(ADDRESS(1,CELL(" & """COL""" & "," & "bs!i67" & ",4)," & """1""" & "," & """" & ")" & "CELL(" & """ROW""" & ",bs!i67)"

Next
End Sub


san2 was created to replace "bs!I67"




The purpose of the code is


That the cell "I67" of sheet named "Bs" is dynamically linked on a summary sheet called Print using vba code. It takes "bs" from cell b1 & "I67" from cell c1




But it is not working pl help to find error in

ActiveCell.FormulaLocal = "=SUBSTITUTE(ADDRESS(1,CELL(" & """COL""" & "," & san2 & ",4)," & """1""" & "," & """" & ")" & "CELL(" & """ROW""" & "," & san2 & ")"