Tab name based on cell; worksheet then takes that tab name to do formulas

[CLCoop]

Don't know if this can be done. I have a workbook that has many tabs. Same data on each tab but representing different states. When you input cell B2: OfficeName it changes tab name.
I'm wanting to consolidates only some of the information from each tab into one worksheet but the name of the tabs will change base on locations input.
ie:
B2 (name of office); B40 leaning date; B41 Total Square footage; B42 Physical address.
There will be several workbooks from each state each with several tabs for each office.
Georgia, 01/02/2020, 10000, 1030 Stone Mountain Way
GeorgiaRome office, 01/31/2020, 500, 200 Rome way

Thinking that if each workbook has a tab (hidden) that consolidated the needed data then when emailed back can quickly copy paste into master workbook or build code to bring it it.
So how do I consolidate the worksheets that TAB names will change into one worksheet. As I can't use ='Office 1!$B2 due to Office 1 name may change. But the name will be in B2.

 

[CLCoop]

I am using on the 1 worksheet the following VBA code to get all the sheet names:
Sub ListALLsheets()
Dim ws As Worksheet
Dim Counter As Integer

Counter = 0
For Each ws In ActiveWorkbook.Worksheets
ActiveCell.Offset(Counter, 0).Value = ws.Name
Counter = Counter + 1
Next ws

End Sub

I then defined Name as sheetnames using: =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")

Then in Column A did 1 - 50, Column B has sheet names, Column C has =Index(sheetname,A1), but now how (VBA) or formula do I use to use the Sheetname now in Column B to get the data from that tab that is in B40 leaning date; B41 Total Square footage; B42 Physical address to populate across.

	Office/Name of tab	Go to Sheet	Physical Office Address	Leasing Date	Total Square Footage	Total Square Footage for RD Only
1​	Leasing	Go to Sheet
2​	Leasing	Go to Sheet	0​
3​	Leasing	Go to Sheet	0​
4​	Lease Changes in FY 21	Go to Sheet	0​
5​	Instructions	Go to Sheet	0​
6​	Summary	Go to Sheet	0​
7​	First	Go to Sheet	0​
8​	Office 1	Go to Sheet	0​
9​	Office 2	Go to Sheet	0​
10​	Office 3	Go to Sheet	0​
11​	Office 4	Go to Sheet	0​
12​	Office 5	Go to Sheet	0​
13​	Office 6	Go to Sheet	0​