VBA Report Split & Merge

PandahNZ

New Member
Joined
Nov 26, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi all,

Trying to set up some VBA code to convert three workbooks with raw data into multiple workbooks with only the data relevant to each region, so far I have Frankensteined a bunch of different VBA code together but have not managed to get it working together yet and figured there must be a more simple way of doing things.

So, I start with three separate workbooks of raw data (can call them RawOne, RawTwo, RawThree).
In column D in each of these workbooks is all of the regions (about 40 different regions, each with maybe a couple hundred rows of data).
My end goal is a separate Workbook for each region, where each of these Region Workbooks has 3 sheets (the relevant data from RawOne, RawTwo & RawThree).
So this would generate about 40 Workbooks, each with three sheets, which then need to be attached in separate emails ready to be sent out to the respective managers of each region.

Apologies if this is a bit confusing, I can't upload the raw files as they have private info - I can however create some mock files to help with the explanation if this would help? Just let me know.

Thanks!
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

PandahNZ

New Member
Joined
Nov 26, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
To try and clarify a bit, I have attached some images - ThreeRawFiles shows a basic version of the three reports I am generating with the different regions in column D.

The remaining three are an example of how one region should end up as one file with separate sheets for each report type (AucklandA, AucklandB & AucklandC).

This would then need to do the same thing for each region so in this example there would be a total of three workbooks created, each with three sheets.

Hope this helps, thanks.
 

Attachments

  • AucklandA.PNG
    AucklandA.PNG
    23.9 KB · Views: 2
  • AucklandB.PNG
    AucklandB.PNG
    24.2 KB · Views: 2
  • AucklandC.PNG
    AucklandC.PNG
    24.8 KB · Views: 2
  • ThreeRawFiles.PNG
    ThreeRawFiles.PNG
    79.1 KB · Views: 2

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!


Cross posted at: VBA Auto Split and Merge Report Data

If you have posted the question at other places, please provide links to those as well.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,738
Messages
5,626,606
Members
416,195
Latest member
tonmcg

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top