Excel Macro: Rename Multiple Worksheets based on List

Hi Excelforum Experts,

I have an excel tool that will consolidate multiple worksheets into one workbook from a desired folder. However, I am having a hard time renaming each worksheet based on list. I have attached the sample workbooks for more details.

Here are the steps that I want to add up on my existing macro codes:

1.) After consolidating multiple worksheets into one, I would like to rename each worksheet based on the list which can be found in the attached workbook named "Conso Tool".
2.) Each current worksheet name has its corresponding desired worksheet name which can be found in "Worksheet Name" tab.
3.) Lastly, the consolidated workbook should be in .xlsx format.

Any inputs and ideas will be highly appreciated. Thank you!

Warmest regards,
Arnel