Merge data from Multiple worksheets into one Master sheet

Dynamically Merge/Consolidate Data from Multiple Worksheets with one formula

Dynamically consolidate data from multiple worksheets into a Summary Worksheet in Excel with the new VSTACK array worksheet function. Great how this new worksheet function works, enter 1 formula and you're done. Note: only working in Excel for Microsoft 365, Excel for Microsoft 365 and for Mac Excel for the web.

Download example workbook
File Date: 16-July-2024

Note: I use the ; as formula separator in my formulas on my Mac, this will automatically change to , in the Consolidate sheets if you use the , as separator. Be aware that if you want to try one of the example formulas on the Information sheet you must change the separator ; to , maybe before you can use them.

Merge/Consolidate worksheet data with VBA macro

The macro examples in the workbook that you can download on this page will add a worksheet with the name RDBMergeSheet to your workbook and will copy cells from every worksheet in the ActiveWorkbook to this worksheet. Each time you run one of the examples it will first delete the summary worksheet named RDBMergeSheet if it exists and then adds a new one to the workbook. This ensures that the data is always up to date after you run the code. In the examples I use a values/formats copy but in the first macro example there is commented example code to copy only the values or everything to the RDBMergeSheet.

No need to change anything in the code to get the macros working in the workbook.

Important: The macro examples use the custom LastRow or LastCol function that you can find in the last module of the workbook. If you want to use the macro examples in your own workbook do not forget to also copy the functions in a standard module of your workbook.

Download Example workbook
File Date: 21-Jan-2021
05/09/2024
Web design by Will Woodgate