How to automatically change the arguments of a formula in a range of cells (all of them in different sheets but in the same position)

[Sorin Toma]

Hi,

I have an workbook with a large number of sheets. One of them (named "Source") is like a "data source sheet" .The other sheets (named from 01 to 99) use some values from "Source" in simple formulas .
My problem is that I need to use same formula but with different arguments in each sheet ( for example: in sheet "01", cell A1= 'Source'!B1" and in sheet "02", same cell A1= 'Source'!B2" and so on) and I'm looking for a solution to to this automatically.
Could someone give me a hint (a formula or a macro) to avoid do this manually?

 

[Legacy 143009]

One non-advisable approach will be:

=INDIRECT("'Source'!B"&INT(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)))

 

[Peter_SSs]

Welcome to the MrExcel board!

Does this do what you want?

Sub InsertFormulas()
  Dim i As Long
  
  For i = 1 To 99
    Sheets(Format(i, "00")).Range("A1").Formula = "=Source!B" & i
  Next i
End Sub