How To Reference Same Cell From Multiple Worksheets In Excel?

At work I create multiple worksheets to store my weekly forecasts and was trying to plot a chart showing the evolution of my forecast week after week. For that I was looking for a way to automatically reference a cell from multiple worksheets on my excel file.

I am not an Excel VBA expert so was afraid the instructions I found here would not work but they did!
===========================================================
Extracted without consent since I was not sure how to ask, all credits should go to the original author of this posting referenced in the following url: https://www.extendoffice.com/documents/excel/1337-excel-reference-same-cell-different-sheet.html

How to reference Same Cell From Multiple Worksheets With some VBA Code

If there are dozens of worksheets, the copy and paste function will be troublesome and time-consuming, in this case, the following VBA code can help you fill the same cell references from multiple worksheets into one worksheet at once.

1. In the Master/last worksheet, click into a cell which is the same cell (same address, for instance C16) that you want to extract from other worksheets.

2. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.

3. Click Insert > Module, and paste the following code in the Module Window.

VBA code: reference same cell from multiple worksheets

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

Sub AutoFillSheetNames() 'Update 20131202 Dim ActRng As Range Dim ActWsName As String Dim ActAddress As String Dim Ws As Worksheet On Error Resume Next xTitleId = "KutoolsforExcel" Set ActRng = Application.ActiveCell ActWsName = Application.ActiveSheet.Name ActAddress = ActRng.Address(False, False) Application.ScreenUpdating = False xIndex = 0 For Each Ws In Application.Worksheets If Ws.Name <> ActWsName Then ActRng.Offset(xIndex, 0).Value = "='" & Ws.Name & "'!" & ActAddress xIndex = xIndex + 1 End If Next Application.ScreenUpdating = True End Sub

4. Then press F5 key to run this code, and all values of that cell from other worksheets will been pulled down into the Master/latest worksheet.

Note: This VBA code is applied for filling the cells where you click. For example, if you click cell A1 in a specified worksheet, all the values of cell A1 from other worksheets will be filled into this worksheet.