VBA for dynamic sheets name + dynamic link + hide sheets based on a cell Value

Question 1

Solution for this question, ( 2020-05-28 22:13:09 Rajesh Kumar )
https://excel.tips.net/T002145_Dynam...Tab_Names.html

---

Question: ( Question 1 )
......I have a list of 80 students. I have made 80 sheets, 1 sheet for 1 student. I want to rename these 80 sheets on the basis of the name in the list, so that whenever I update the name list, the corresponding sheet-name changed automatically. I'm a beginner in this field. Please help me.

Solution.
Hello Rajesh
This requirement is fairly easy with VBA

There are 3 macros which I have written for you, and I am returning 2 workbook examples

Macro for your original requirement
Private Sub Worksheet_Change(ByVal Target As Range)
This macro is in both workbooks:
It does this: If you change any of your names in column B of the worksheets, then the name of the corresponding worksheet tab Name will change, as per your main original requirement.

Workbook AddNamesfromListToExistingWorksheets.xlsm
This is the workbook supplied by you. It has initially 80 student names in column B of the first worksheet. It has 80 additional worksheets , as made by you, with the names of 1 2 3 4 5 …. Etc.
This workbook has a macro , Sub ChangeNamesToExistingWorksheets() . This macro replaces those names with the names from the Student name list in column B

Workbook AddWorksheetsNamedFromList.xlsm
This is your original Workbook, with all but the first worksheet deleted. So this only contains one worksheet containing your list of student Names in column B
In this workbook, there is a macro, Sub AddWorksheetsfromListOfNames()
This macro adds worksheets with the student Names

---

Note: in your supplied data, you had two identical names at row 26 and at row 75, SACHIN KUMAR , so I changed it to SACHIN KUMAR 2 in row 75
( We could handle such cases in coding, automatically, later if you preferred )


Alan

---

Workbooks:
Share ‘AddNamesfromListToExistingWorksheets.xlsm’ : https://app.box.com/s/2ytj6qrsyaudh3tzgtodls8l05zn1woz
Share ‘AddWorksheetsNamedFromList.xlsm’ : https://app.box.com/s/yljwyk5ykxtjt2qhzvdpwcrft19phx54
For macros, see also post https://excelfox.com/forum/showthrea...ll=1#post13444

---

Cross posts
https://excel.tips.net/T002145_Dynamic_Worksheet_Tab_Names.html ( 2020-05-28 22:13:09 Rajesh Kumar ) https://excel.tips.net/T002145_Dynam...Tab_Names.html
https://www.mrexcel.com/board/threads/vba-for-dynamic-sheets-name-dynamic-link-hide-sheets-based-on-a-cell-value.1135674/ https://www.mrexcel.com/board/thread...value.1135674/

Macros for this post ( Question 1 )
https://excelfox.com/forum/showthrea...ll=1#post13443

Code:

---

Option Explicit

'  https://excel.tips.net/T002145_Dynamic_Worksheet_Tab_Names.html  '    https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13443&viewfull=1#post13443    https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13443&viewfull=1#post13444

Sub RemoveAllButThisWorksheet()
Dim Cnt
    For Cnt = ThisWorkbook.Worksheets.Count To 2 Step -1 ' second worksheet counting tab from the left is worksheets item 2
    Let Application.DisplayAlerts = False
    ThisWorkbook.Worksheets.Item(Cnt).Delete
    Let Application.DisplayAlerts = True
    Next Cnt
End Sub
Sub ChangeNamesToExistingWorksheets() '
Rem 0
On Error GoTo Bed                      '      If we have problems then we want to make sure that we still  re enable  Events coding before ending the macro
 Let Application.EnableEvents = False  '      This will prevent anything we do in this macro from causing erratic working of any automatic event coding
Rem 1 worksheets 1 info
Dim Ws1 As Worksheet
 Set Ws1 = ThisWorkbook.Worksheets.Item(1) ' or Worksheets("Name List")    '  first worksheet counting tab from the left is worksheets item 1
Dim Lr1 As Long
 Let Lr1 = Ws1.Range("B" & Ws1.Rows.Count & "").End(xlUp).Row '    http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11466&viewfull=1#post11466      Making Lr dynamic ( using rng.End(XlUp) for a single column. )
Dim arrNmes() As Variant              '      The  .Value2  property in the next line will return a field of values housed in Variant type Elements, so we need to  give the variant type to our array used to capture that  array  of values
 Let arrNmes() = Ws1.Range("B1:B" & Lr1 & "").Value2
Rem 2 Add and name worksheets from list
Dim Cnt As Long
    For Cnt = 2 To UBound(arrNmes(), 1) ' From (2,1) To (2,Lr1) in names array list column 2 ( column B )
    Let Worksheets.Item(Cnt).Name = arrNmes(Cnt, 1)
    Next Cnt
Bed:  ' error handling code section.
 Let Application.EnableEvents = True
End Sub

Sub AddWorksheetsfromListOfNames()
Rem 0
On Error GoTo Bed
 Let Application.EnableEvents = False
Rem 1 worksheets 1 info
Dim Ws1 As Worksheet
 Set Ws1 = ThisWorkbook.Worksheets.Item(1) ' or Worksheets("Name List")    '  first worksheet counting tab from the left is worksheets item 1
Dim Lr1 As Long
 Let Lr1 = Ws1.Range("B" & Ws1.Rows.Count & "").End(xlUp).Row '    http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11466&viewfull=1#post11466      Making Lr dynamic ( using rng.End(XlUp) for a single column. )
Dim arrNmes() As Variant
 Let arrNmes() = Ws1.Range("B1:B" & Lr1 & "").Value2
Rem 2 Add and name worksheets from list
Dim Cnt As Long
    For Cnt = 2 To UBound(arrNmes(), 1) ' From (2,1) To (2,Lr1) in names array list column 2
    Worksheets.Add After:=Worksheets.Item(Worksheets.Count)
    Let ActiveSheet.Name = arrNmes(Cnt, 1)
    Next Cnt
   
Bed:
 Let Application.EnableEvents = True
End Sub
'
'

Private Sub Worksheet_Change(ByVal Target As Range)
    If IsArray(Target.Value) Then Exit Sub ' If we have changed more than 1 cell, our code lines below will error, so best do nothing in such a case
Dim Ws1 As Worksheet
 Set Ws1 = Me
Dim Lr1 As Long
 Let Lr1 = Ws1.Range("B" & Ws1.Rows.Count & "").End(xlUp).Row '    http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11466&viewfull=1#post11466      Making Lr dynamic ( using rng.End(XlUp) for a single column. )

Dim Rng As Range
 Set Rng = Ws1.Range("B2:B" & Lr1 & "")
    If Not Intersect(Rng, Target) Is Nothing Then ' The Excel VBA Application.Intersect method returns the range where all the given ranges cross, or  Nothing  if there are no common cells.  So, in this example,  we would have  Nothing  if our selection ( which VBA supplies in Target ) , did not cross our names list      '  https://docs.microsoft.com/en-us/office/vba/api/excel.application.intersect
    Dim Rw As Long
    Let Rw = Target.Row
    Let ThisWorkbook.Worksheets.Item(Rw).Name = Target.Value ' In the list, each row number corresponds to the item number of our worksheets made from that list
    Else
    ' changed cell was not in Student name list
    End If

End Sub


---

---

Cross posts
https://excel.tips.net/T002145_Dynamic_Worksheet_Tab_Names.html ( 2020-05-28 22:13:09 Rajesh Kumar ) https://excel.tips.net/T002145_Dynam...Tab_Names.html
https://www.mrexcel.com/board/threads/vba-for-dynamic-sheets-name-dynamic-link-hide-sheets-based-on-a-cell-value.1135674/ https://www.mrexcel.com/board/thread...value.1135674/

Macro for these posts ( Question 2 )
https://excelfox.com/forum/showthrea...ll=1#post13442
https://excelfox.com/forum/showthrea...ll=1#post13448

Code:

---

'  _1. I want to create 5 tabs (Sheets) on the basis of these 5 names. (Now the workbook will have 6 tabs, including Master Sheet)  https://excelfox.com/forum/showthread.php/2501-VBA-for-dynamic-sheets-name-dynamic-link-hide-sheets-based-on-a-cell-Value?p=13445#post13445
Sub AddWorksheetsfromListOfNames2() '  https://excelfox.com/forum/showthread.php/2501-VBA-for-dynamic-sheets-name-dynamic-link-hide-sheets-based-on-a-cell-Value?p=13445#post13445    https://www.mrexcel.com/board/threads/vba-for-dynamic-sheets-name-dynamic-link-hide-sheets-based-on-a-cell-value.1135674/
Rem 0
On Error GoTo Bed                      '      If we have problems then we want to make sure that we still  re enable  Events coding before ending the macro
 Let Application.EnableEvents = False  '      This will prevent anything we do in this macro from causing erratic working of any automatic event coding
Rem 1 worksheets 1 info
Dim Ws1 As Worksheet
 Set Ws1 = ThisWorkbook.Worksheets.Item(1) ' or Worksheets("Master Sheet")    '  first worksheet counting tab from the left is worksheets item 1
Dim Lr1 As Long
 Let Lr1 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row '    http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11466&viewfull=1#post11466      Making Lr dynamic ( using rng.End(XlUp) for a single column. )
Dim arrNmes() As Variant
 Let arrNmes() = Ws1.Range("A1:A" & Lr1 & "").Value2
Rem 2 Add and name worksheets from list
Dim Cnt As Long
    For Cnt = 1 To UBound(arrNmes(), 1) ' From (2,1) To (2,Lr1) in names array list column 1 ( Column A )
    Worksheets.Add After:=Worksheets.Item(Worksheets.Count)
    Let ActiveSheet.Name = arrNmes(Cnt, 1)
    Next Cnt

Bed:
 Let Application.EnableEvents = True
End Sub  '      (Now the workbook will have 6 tabs, including Master Sheet)

Sub AddHypolinkToWorksheet()  '  https://www.mrexcel.com/board/threads/vba-for-dynamic-sheets-name-dynamic-link-hide-sheets-based-on-a-cell-value.1135674/
Rem 0
On Error GoTo Bed                      '      If we have problems then we want to make sure that we still  re enable  Events coding before ending the macro
 Let Application.EnableEvents = False  '      This will prevent anything we do in this macro from causing erratic working of any automatic event coding
Rem 1 worksheets 1 info
Dim Ws1 As Worksheet
 Set Ws1 = ThisWorkbook.Worksheets.Item(1) ' or Worksheets("Master Sheet")    '  first worksheet counting tab from the left is worksheets item 1
Dim Lr1 As Long
 Let Lr1 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row '    http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11466&viewfull=1#post11466      Making Lr dynamic ( using rng.End(XlUp) for a single column. )
Dim arrNmes() As Variant
 Let arrNmes() = Ws1.Range("A1:A" & Lr1 & "").Value2

Rem 2 Add hyperlinks
 Ws1.Hyperlinks.Delete
Dim Cnt
    For Cnt = 1 To Lr1        '  ='F:\Excel0202015Jan2016\OffenFragensForums\AllenWyatt\[DynamicWorksheetNamesLinkHideBasedOnCellValue.xlsm]RAHIM'!$A$1
    Dim Paf As String: Let Paf = "='" & ThisWorkbook.Path & "\[" & ThisWorkbook.Name & "]" & arrNmes(Cnt, 1) & "'!$A$1"
'    Ws1.Hyperlinks.Add Anchor:=Ws1.Range("A" & Cnt & ""), Address:="", SubAddress:="='" & arrNmes(Cnt, 1) & "'!A1", ScreenTip:=arrNmes(Cnt, 1), TextToDisplay:=arrNmes(Cnt, 1)
    Ws1.Hyperlinks.Add Anchor:=Ws1.Range("A" & Cnt & ""), Address:="", SubAddress:=Paf, ScreenTip:=arrNmes(Cnt, 1), TextToDisplay:=arrNmes(Cnt, 1)
    Next Cnt
Bed:  ' error handling code section.
 Let Application.EnableEvents = True
End Sub



Private Sub Worksheet_Change(ByVal Target As Range)
    If IsArray(Target.Value) Then Exit Sub ' If we have changed more than 1 cell, our code lines below will error, so best do nothing in such a case
Dim Ws1 As Worksheet
 Set Ws1 = Me
Dim Lr1 As Long
 Let Lr1 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row '    http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11466&viewfull=1#post11466      Making Lr dynamic ( using rng.End(XlUp) for a single column. )

Dim Rng As Range
 Set Rng = Ws1.Range("A1:A" & Lr1 & "")
    If Not Intersect(Rng, Target) Is Nothing Then ' The Excel VBA Application.Intersect method returns the range where all the given ranges cross, or  Nothing  if there are no common cells.  So, in this example,  we would have  Nothing  if our selection ( which VBA supplies in Target ) , did not cross our names list      '  https://docs.microsoft.com/en-us/office/vba/api/excel.application.intersect
    Dim Rw As Long
    Let Rw = Target.Row
        If Target.Value = "" Then '  5. If I delete the content of A1 (ANUJ), i.e, if cell A1 is blank, the corresponding sheet "ANUJ" should hide automatically.
        ThisWorkbook.Worksheets.Item(Rw + 1).Visible = False
        Exit Sub
        Else
        ThisWorkbook.Worksheets.Item(Rw + 1).Visible = True
        Let ThisWorkbook.Worksheets.Item(Rw + 1).Name = Target.Value ' In the list, each row number corresponds to one less than the item number of our worksheets made from that list
        End If
    Else
    ' changed cell was not in Student name list
    End If

'
Call AddHypolinkToWorksheet
End Sub

---

---

Share ‘DynamicWorksheetNamesLinkHideBasedOnCellValu e. : https://app.box.com/s/louq07ga6uth1508e572l7zr9fakont9

Thank you DocAElstein Sir for the best solution.



https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.eileenslounge.com/viewtopic.php?p=296754#p296754
https://www.eileenslounge.com/viewtopic.php?p=296859#p296859
https://teylyn.com/2017/03/21/dollarsigns/#comment-191
https://www.excelfox.com/forum/showthread.php/2918-Right-Hand-Side-Range-Range-Value-values-Range-Range-Value-only-sometimes-Range-Range-Value-Anomaly
https://www.excelfox.com/forum/showthread.php/2355-Tests-and-Notes-on-Range-Referrencing/page8
https://www.eileenslounge.com/viewtopic.php?p=296859#p296859
https://www.excelfox.com/forum/showthread.php/2355-Tests-and-Notes-on-Range-Referrencing?p=24006&viewfull=1#post24006
https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=23185&viewfull=1#post23185
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

Question 2

I have a workbook which contain ONLY 1 sheet named as "Master Sheet". On Master Sheet there is a list of 5 names, as
A1=ANUJ
A2=RITA
A3=MUKESH
A4=RAM
A5=RAHIM

(Actually I have total 400 names, but for easiness I have taken only 5)

Now, my requiremet is:
1. I want to create 5 tabs (Sheets) on the basis of these 5 names. (Now the workbook will have 6 tabs, including Master Sheet)

2. Tab name should be dynamic. Means, if I change the value of A1 from 'ANUJ' to 'SHONA' on Master Sheet, then the "ANUJ" tab should automatically be renamed as "SHONA", without going to the tab "ANUJ". (Because going to 400 tabs to make it "ActiveSheet" is very much time consuming)

3. Each tab should be linked with the corresponding list name, that is, If I click on "A1" (ANUJ) on Master Sheet, the tab (Sheet) "ANUJ" will be opened.

4. Also, this linking should be dynamic, that is, if I change the name "ANUJ" as "SHONA" on Master Sheet, the tab "ANUJ" will be renamed as "SHONA" and remain linked with Cell A1 of Master Sheet.

5. If I delete the content of A1 (ANUJ), i.e, if cell A1 is blank, the corresponding sheet "ANUJ" should hide automatically.


In simple lines,
I want a workbook which contain
- dynamic sheet name (without going to the sheet (VBA code without "ActiveSheet")
- dynamic hyperlink
- auto hide feature for any sheet

Thanks in advance!


I'm a counsellor and preparing a Record of all students. If someone helps, I'll really be grateful. Please help if possible.

---

Cross Posts:
2020-05-29 23:28:19 PRAVEEN https://excelribbon.tips.net/T007993...Tab_Names.html
https://www.mrexcel.com/board/thread...names.1135624/
https://ask.wellsr.com/1457/dynamic-sheets-name-dynamic-link-hide-sheets https://ask.wellsr.com/1457/dynamic-...nk-hide-sheets

Hello Anshu ( PRAVEEN )
Welcome to excelfox.
See if this helps get you started: https://excelfox.com/forum/showthrea...ll=1#post13443

I can look at the further requirements tomorrow

( If you post the same question elsewhere, and/ or get a solution in the meantime, then please tell us where, and post the solution.
Please always tell everybody where you duplicate a question.
Thanks,
Alan )

Question 2


Hi

Macros and File Solution Here: https://excelfox.com/forum/showthrea...ll=1#post13447


Explantion:
_ 1. create 5 tabs (WorkSheets) on the basis of names list
Sub AddWorksheetsfromListOfNames2() answers your first question. ( It works similarly to the other macro I did for your, Sub AddWorksheetsfromListOfNames()

_2. dynamicÂ… Means, if I change the value of A1 from 'ANUJ' to 'SHONA' on Master Sheet, then the "ANUJ" tab should automatically be renamed as "SHONA Â….
This is basically answered by my previous macro written for you, Private Sub Worksheet_Change(ByVal Target As Range) ( https://excelfox.com/forum/showthrea...ll=1#post13444 )

_ 3. – _5.
Some of this is new stuff for me, so my solution may be far from the best
_3. Each tab should be linked with the corresponding list name, that is, If I click on "A1" (ANUJ) on Master Sheet, the tab (Sheet) "ANUJ" will be opened.
Sub AddHypolinkToWorksheet() seems to do this.

_4. Also, this linking should be dynamic, that is, if I change the name "ANUJ" as "SHONA" on Master Sheet, the tab "ANUJ" will be renamed as "SHONA" and remain linked with Cell A1 of Master Sheet.
This can be achieved by adding a code line within Private Sub Worksheet_Change(ByVal Target As Range) , to re run Sub AddHypolinkToWorksheet() seems to do this.

Code:

---

'
Call AddHypolinkToWorksheet

---

This may not be the most efficient way to do this.


_5. If I delete the content of A1 (ANUJ), i.e, if cell A1 is blank, the corresponding sheet "ANUJ" should hide automatically.
To achieve this, a modification in Private Sub Worksheet_Change(ByVal Target As Range) seems to work:
Changing .._

Code:

---

        Let ThisWorkbook.Worksheets.Item(Rw + 1).Name = Target.Value ' In the list, each row number corresponds to one less than the item number of our worksheets made from that list

---

_.. to

Code:

---

        If Target.Value = "" Then '  5. If I delete the content of A1 (ANUJ), i.e, if cell A1 is blank, the corresponding sheet "ANUJ" should hide automatically.
        ThisWorkbook.Worksheets.Item(Rw + 1).Visible = False
        Exit Sub
        Else
        ThisWorkbook.Worksheets.Item(Rw + 1).Visible = True
        Let ThisWorkbook.Worksheets.Item(Rw + 1).Name = Target.Value ' In the list, each row number corresponds to one less than the item number of our worksheets made from that list
        End If
    Else

---

_._________________

Here are the macros and workbook which also contains the macros: https://excelfox.com/forum/showthrea...ll=1#post13447

Alan

P.S.If this solution is satisfactory, It is customary to inform at the other forums where you have cross posted, of your solution, so as to avoid others wasting time.

---

SOLVED!
Excellent Sir, Very Very Thanks to you!!

Thanks for the feedback.
( I have merged your two questions because they were similar ( I have put them together here, in this Thread ) )

Alan

Sir, there is a problem in this file...https://app.box.com/s/louq07ga6uth1508e572l7zr9fakont9

When I delete the content of last cell in the list, i.e., A5 (RAHIM), the corresponding sheet "RAHIM" remains visible. The macro to hide the sheet works for all cells, except the last one in the list. I don't know how to fix it.


I also have a small request...
I want to shift the list from Range A1:A5 to Range C4:C9, that is, the list of name will be started from the cell C4 instead of the cell A1.

Would you please make these changes in this excel workbook. (I've tried a lot, but failed everytime)

Hi
_....When I delete the content of last cell in the list, i.e., A5 (RAHIM), the corresponding sheet "RAHIM" remains visible. The macro to hide the sheet works for all cells, except the last one in the list….
I think I see the problem: In the scenario that the last used cell is empty, then the coding gets the last row calculation, Lr1 , wrong: The value comes out one less than we want.

This is one way to over come this, ( there may be better ways… ):
We need
_ an extra event macro which records the cell range which was last used in column 1 ( Column A )

Code:

---

Private Sub Worksheet_SelectionChange(ByVal Target As Range)  '    https://excelfox.com/forum/showthread.php/2501-VBA-for-dynamic-sheets-name-dynamic-link-hide-sheets-based-on-a-cell-Value?p=13456&viewfull=1#post13456
    If Target.Column = 1 And Not IsArray(Target.Value) Then ' we are in column A ,  And  we selected one cell
    Set LRng = Target
    Else

    End If
End Sub

---

_ This cell range is recorded in a “global” variable, that is to say one which goes outside of any procedure , towards the top of the code module:

Code:

---

Option Explicit
Dim LRng As Range

---

_ Finally, a check is made within Private Sub Worksheet_Change(ByVal Target As Range) which attempts to check for the scenario, and correct the calculated value of Lr1

Code:

---

    If Not LRng Is Nothing And Target.Value = "" And LRng.Row = Lr1 + 1 Then Let Lr1 = Lr1 + 1
Dim Rng As Range

---

I have not checked this solution thoroughly. I leave that to you.

File: ( with modified macros )
DynamicWorksheetNamesLinkHideBasedOnCellValue2.xlsm : https://app.box.com/s/dgklq01wbxrvxxurjd8wccr2ghh9vlze

---

---

….I want to shift the list from Range A1:A5 to Range C4:C9, that is, the list of name will be started from the cell C4 instead of the cell A1….

I have adjusted the macros for a range in column C starting at row 4:
Macro here:
https://excelfox.com/forum/showthrea...ll=1#post13457
File:
DynamicWorksheetNamesLinkHideBasedOnCellValueC.xlsm : https://app.box.com/s/alo1fbzx8r41jd81rttghikytqzvm0w9

---

Alan

THANKS A LOT SIR!!
WORKS FANTASTIC....


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.eileenslounge.com/viewtopic.php?p=296754#p296754
https://www.eileenslounge.com/viewtopic.php?p=296859#p296859
https://teylyn.com/2017/03/21/dollarsigns/#comment-191
https://www.excelfox.com/forum/showthread.php/2918-Right-Hand-Side-Range-Range-Value-values-Range-Range-Value-only-sometimes-Range-Range-Value-Anomaly
https://www.excelfox.com/forum/showthread.php/2355-Tests-and-Notes-on-Range-Referrencing/page8
https://www.eileenslounge.com/viewtopic.php?p=296859#p296859
https://www.excelfox.com/forum/showthread.php/2355-Tests-and-Notes-on-Range-Referrencing?p=24006&viewfull=1#post24006
https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=23185&viewfull=1#post23185
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA