I have developed a VBA macro that combines data from multiple sheets (Sheet1, Sheet2, Sheet3 etc) and creates a new sheet ("ConsolidatedData") that combines data from all sheets.

Then I have another Sheet ("Update Value") that performs a Vlookup on sheet ("ConsolidatedData") - This referencce is broken as I run the macro. Reason being the macro deletes an old "ConsolidatedData" sheet and adds a new "ConsolidatedData" sheet.

I understand why this is happening. I just need to update the lookup function so it refers to the sheet name i.e. "ConsolidatedData" anytime i calculate the Vlookup function.

Thanks!