Link same cells across multiple sheets

[Anshu]

In the worksheet attached here (please see the attachment), there are total 82 sheets.
1st Sheet, Master sheet, contains a list of 80 names.
2nd sheet is "Case", and remaining 80 sheets are 1, 2, 3.....80.

I want to link each name of the list in Master Sheet with the same cells of each corresponding sheet,
that is,
the name in cell B2 should appear in cell C3 of sheet 1
similarly, value of B3 in cell C3 of sheet 2
similarly, value of B4 in cell C3 of sheet 3
and so on

Only the 2nd sheet 'Case' should be remain unaffected

...................................

I can link by using the formula ='Master Sheet'!B2
But I have to go to 80 sheets one by one and link each cell one by one, which is really takes lot of time.

Is there any simple method or vba to solve this problem??

Please help!!

[DocAElstein]

Hi,
There may be a simple way to do this that I am personally do not know about. I am not sure.

But what you want can be achieved with some simple coding.
If you apply the .Value property to the range object of a single cell, then that has an effect very similar to you writing into a cell manually: If you have default options, then “what you see” in the text in a cell is the displayed .Value property. Correspondingly it seems to work that using the .Value property with coding has an effect like you writing into a cell.

You can manually write in a cell a formula, so correspondingly you can put that formula into a cell, just as you would write it , via the .Value property

This is one macro example that will do put your link in

Sub PutLinkFormulaInCellC3OfWorksheets()
Rem 1 worksheet data info
Dim WsM As Worksheet: Set WsM = ThisWorkbook.Worksheets("Master Sheet")
Dim LrM As Long: Let LrM = WsM.Range("A" & WsM.Rows.Count & "").End(xlUp).Row
Dim arrDta() As Variant: Let arrDta() = WsM.Range("A1:A" & LrM & "").Value2
Rem 2
Dim Cnt As Long
For Cnt = 2 To LrM
Let ThisWorkbook.Worksheets("" & arrDta(Cnt, 1) & "").Range("C3").Value = "='Master Sheet'!B" & arrDta(Cnt, 1) + 1 & ""
Next Cnt
End Sub

If you only want to put the value in, then this macro example will do

Sub PutValueInCellC3OfWorksheets()
Rem 1 worksheet data info
Dim WsM As Worksheet: Set WsM = ThisWorkbook.Worksheets("Master Sheet")
Dim LrM As Long: Let LrM = WsM.Range("A" & WsM.Rows.Count & "").End(xlUp).Row
Dim arrDta() As Variant: Let arrDta() = WsM.Range("A1:B" & LrM & "").Value2
Rem 2
Dim Cnt As Long
For Cnt = 2 To LrM
Let ThisWorkbook.Worksheets("" & arrDta(Cnt, 1) & "").Range("C3").Value = arrDta(Cnt, 2)
Next Cnt
End Sub

The various maths logic and other stuff in the coding is similar to in some of your other stuff

Alan

P.S:
Important Note: Because you are using numbers as your worksheet names, you must do this sort of thing
("" & arrDta(Cnt, 1) & "")
or this
( arrDta(Cnt, 1) & "")
or this
("" & arrDta(Cnt, 1) )

If you do not do either of those, and instead do this, (arrDta(Cnt, 1) ) , then you may reference the wrong worksheet.

To explain
In your worksheet, your third worksheet is named 1

These will reference it correctly
Worksheets.Item("1")
or
Worksheets("1")
Using a string will make VBA look for the string name.


These will reference the first worksheet ( “Master Sheet” )
Worksheets.Item(1)
or
Worksheets(1)
Using a number will make will make VBA look for the tab item number , ( = the tab number counting from the left )

[Anshu]

Thank you for the code! It works very well.