Macro To Pull Data From Dynamic Named Multiple Worksheets Weekly Into A Master Sheet

Attached is a master metrics deck, where data needs to be refreshed every week, from two different workbooks.

The catch is, the two parent workbooks would be new versions every week (data would be same format, and style within), but file name would change each week, for eg: for 1st workbook, 'XXX_Weekly_Week24.xlsx' to 'XXX_Weekly_Week25.xlsx' and so on.

for 2nd workbook, 'YYY_Weekly_Week24.xlsx' to 'YYY_Weekly_Week25.xlsx' and so on.

In the master metrics deck, a consolidated vew of some of data from workbook 1 and workbook 2 is presented for each week.

This master file would remain same, and only be refreshed each week (preferably with a button on its sheet "REFRESH!!")

Next, need to manually change cells C2:G2 each week to reflect week titles correctly.

For e.g.: week6 would show week 1 through week 5, but during week7, it needs to show week 2 to week 6. Only last five weeks of data any given time.

The first datarows of each set are conditionally formatted (they are compared with other rows in the set and need to reflect danger values, good values etc) (I have done that already).