Firstly, thank you for your help in advance.

I have the following code for a macro which opens up necessary file and runs the code. The issue I am facing is that, the macro is not running in all the tabs but only in the tab that it opens in.

I want a hyperlink on every worksheet/tab in cell M1 and linking it back to the Index tab cell A1.

The code is below,

Sub Hyper11()

fnameList = Application.GetOpenFilename(FileFilter:="Microsoft Excel Workbooks (*.xls;*.xlsx;*.xlsm),*.xls;*.xlsx;*.xlsm", Title:="Choose Excel files to add hyperlink in each tab", MultiSelect:=False)
Set wbkSrcBook = Workbooks.Open(Filename:=fnameList)

Dim Ws As Worksheet

For Each Ws In Application.Worksheets

ActiveCell.FormulaR1C1 = "=HYPERLINK(""#'Index'!A1"",""Index"")"

Next Ws

End Sub