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.I’ve amended the original code to enable me to set a column for the month and define the source as either Flex or Freeze data and as I’ve shown it above, it will rapidly group defined worksheets (or tabs) into one summary workbook eg.month 6 outpatient data from 5 PCT files consolidated into 1 summary workbook.The example spreadsheet can be downloaded from the Downloads section at the end of this article. A good way to do this is to draw up your own mapping table similar to that shown above. Copy to Range = out Col Name & insert At Row Num & ":" & out Col Name & (insert At Row Num from Range To Copy. There are several parts of the code that are involved in the mapping of columns from each spreadsheet into the output. With the mapping defined, there are a few places in the code where the mapping is used. The difference is that here you will be dealing with spreadsheets in different formats and so will need to map values from columns in each spreadsheet into the correct column in your desired output spreadsheet.
I have chosen to go with the latter option so that I can easily flick through each months SLAM folders.This particular culprit has somewhere in the region of 20 tabs relating to a particular care type – such as outpatients, admitted patient care, A&E etc, we then get 5 copies of this for each PCT.Multiply this by 12 for each month of the year and you start to have quite an horrendous copy and paste task if you want to pull it all together manually.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.
Over the course of business, you may find yourself with copious Excel files that you want to combine into a single workbook to make life a little easier.