在特定工作表上填充具有命名范围的数组

I am new to VBA and trying to automate populating an array using a named range on a specific worksheet. The array in question is in bold in below code. The named range is "SheetNames", on a worksheet named "Consolidation", cell B3 to B101.I am new to VBA and trying to automate populati