Coping Data from 800+ Sheets to Master Table using VBA

New to using VBA for anything other than simple recorded Macros.

I've inherited a workbook that is currently using indirect formulas to refence cells across the workbook. The problem with that is there's about 800 worksheets and 125 columns in the master table. In order for to workbook to be functional currently auto calculate formulas is off and there's a macro to enable it to refresh the data. There's an effort to put this into a database but that's going to take longer than then our current needs.

I want to get rid of the indirect formulas to make the workbook more stable.

I need to build a master table that displays the data from each worksheet. The worksheets are form based so it's not simply coping several tables into one. I've tried recording a macro to do the copy and it works partially. It's super massive with the 125 fields and doesn't shift to the next row as we move to the next worksheet.

If someone could help on making the code cleaner and help on moving to the next row I'd appreciate it. Below is a shorter version of the code, only 2 examples not all 125.

Please Login or Register  to view this content.

Hello,

With your description of the problem :
1. New to VBA
2. 800 + sheets to combine
3. The current macro which is far from 'ideal'
....

Would recommend to start from scratch and use Power Query ...

I agree with Carim. PQ may be a more efficient way as it will continually update changes.

To better understand PQ,

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.