Vba to populate inputs on multi Sheets with Index Sheet and hyperlink between

Hi everybody,

As being seen in my attached example file which is also very similar to my original, I have 4 constant sheets and lots of dynamic sheets of Hotels can be extended 1500 in total. The yellow tabs (first 3 sheets) are mostly hidden and the rest starting from INDEX Sheet will always be visible. On INDEX Sheet, I have 3 main locations and 4 columns under each location which are referring to the information in Hotels' Sheets. The range adresses and sizes of the referred information on Hotels' sheets are always constant and INDEX sheet row number leads to 507 can host 500 hotels in each location. You may also see some explonations on INDEX sheet top.

So I wonder how a macro can compare the locations on INDEX with Hotels Sheets and auto fill the required information in each column with respect to hotels' sheets then auto hyperlink the Hotel names on INDEX with appropriate Hotel' s tab?

Thanks in advance for your kindest helps.