Macro that splits a Master data workbook into multiple workbooks

Bobstar

New Member
Joined
Oct 7, 2020
Messages
24
Office Version
  1. 2019
Platform
  1. Windows
Hi

I’m looking for help with a macro that splits a Master data workbook into multiple workbooks. The Master data workbook has two sheets:
  • Client data (first screenshot) – each row contains different client details, going to thousands of clients.
  • Accounts data (second screenshot)– there are multiple rows for one client.
Client ID is a unique column linking both sheets.

1691446286096.png


1691446317606.png


The macro should do the following:
  • Create a new workbook
  • Rename first sheet as ‘first client ID & client data’ in this case it will be ‘2 client data’.
  • From the Master data workbook, client data sheet, copy and paste the row with respective client data retaining the header.
  • Add a new sheet and rename it as first client ID & account data. In this case it will be ‘2 account data’.
  • From the Master data workbook, account data sheet, copy and paste all the rows with that respective client ID.
  • Repeat steps 2 to 5 for the other client IDS upto a max of 10 clients.
  • The sheets in this workbook should look as below
1691446382380.png

  • Save this workbook down as ‘Data 1’ in the same folder as the Master data workbook.
  • Create a new workbook and repeat the above steps for the next 10 client IDS and then save as ‘Data 2’.
  • Repeat until all data in the Master data workbook is copied.
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

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
Back
Top