Macro for consolidating excel files

Rated 3.86/5 based on 627 customer reviews

Here I introduce Kutools for Excel’s powerful Combine function for you.

If in some cases, you want to merge data with same headers only, and in other cases, you want to merge data and consolidate them, are there any tricks can solve both these tasks?

I’ve been working on some VBA macro code to pull the data range from each tab of the monthly return and then stack them on top of each other.

I found snippets of code all over the web but I think the original code writer was Ron de Bruin who has some excellent VBA macro examples.

Ron de Bruin offers a number of ways to work with the different workbooks, such as working through all workbooks in a set folder or looping through a user defined selection of workbooks.

I have chosen to go with the latter option so that I can easily flick through each months SLAM folders.

I would now like to amend the code so that it will automatically loop through an array of worksheet names as each month’s report is opened so that outpatients and A&E and APC etc are consolidated in one go.

At the moment I have to change the worksheet name for each care group, so it is still quite a manual process.

Here, Office Tab supports similar processing, which allow you to browse multiple Excel workbooks or Word documents in one Excel window or Word window, and easily switch between them by clicking their tabs. Then in the Consolidate window, do as below operation: 1) Select the calculation you want form Function list;2) Click to select the range you want to merge.3) Click Add button to add the range into All reference list.

For example, you have some data with the same headers in different sheets as below screenshots shown, and now your job is to merge these sheets into one sheet. The advantage of Kutools for Excel’s Combine function is that you can merge multiple sheets across opened workbooks or unopened workbooks into one worksheet or one workbook.

Instead of copying and pasting them one by one, this tutorial introduces the better ways for you to handle this job. Supposing you are working with a worksheet which contains multiple duplicate records, and now you need to combine / merge the rows based on the same value and do some calculations, such as sum, average, count of the duplicate rows.

You may be familiar to view multiple webpages in Firefox/Chrome/IE, and switch between them by clicking corresponding tabs easily.

Press F5 key to run the VBA, and a dialog pops out for you to enter the number of the title rows. Tip: (1.) Your data must start from A1, if not, the code will not take effect. (3.) This code only can combine all worksheets of the active workbook, if you want to merge worksheets from multiple workbooks, this code will not work.

Leave a Reply