Consolidating all worksheets (different workbook) into one workbook

Hi

I'd recommend you take a look at Power Query for consolidating data from files

(assuming you have Excel 2010 or later)

I used to use VBA to do what you are doing but now Power Query is much simpler 

Here's a slightly out of date video of how it works,  these days Power Query makes it even easier ot combine files with the Combine + Edit option

https://accessanalytic.com.au/power-query-get-transform-introduction-part-1/