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.

How to rename a Bluetooth device on Windows 10 - a problem with TWS earbuds - air-pods knockoff

I own a pair of Bluetooth air-pods knockoffs from China - i7S-TWS from TWS I assume. The actual brand changes depends where and who do you buy it from but I think they are manufactured by the same company.

In any case, you can use each of the earbud separately or connected but I always struggle when connecting those to my Windows Machine since I never know which one is the left and which one is the right one since they both show up on the device list as tws-i7 !

I was looking for a way to rename those but I do not have that option on the Show Bluetooth Devices window below:

I looked for a solution and finally found the answer!
1) Open your file explorer and on the address/url go to the following address:
Control Panel\All Control Panel Items\Devices and Printers

2) The following will open:

3) Do a right click on the device you want to change the name, select Properties - Bluetooth

4) Edit the device name and hit apply! he device name will remain the same! SORRY, this is just to show you the problem!
5) The are some extra careful steps you will need to take to make this work!

• Make sure that your device is actually connected to your pc before starting step 3
• then repeat step 3 and 4, but this time, wait, DO NOT hit apply yet! Turn off your Bluetooth device first (physically on your device side, not on windows), THEN HIT APPLY on the properties window above and then OK. This time the device name will  change
• reconnect the bluetooth device and it will now show on the Bluetooth & Other devices with the updated name!

Hope this works for you! Pass this blog post around if you found this useful!