mrrmpc
asked on
How-to split group data in a main worksheet into multiple worksheets - Excel
MS Excel 2010
I have a workbook that has one main worksheet, named Data.
The data worksheet then has information that is in various Group, grouped by segment type eg MSH, EVN, PID, PD1 etc. We didn't create the base document, it was provided by vendor for use.
We need to retain the Data tab and all of its information in current state. The ask is to create additional worksheets for each group. And for each new worksheet created it should keep the column headers in row-1 of the Data tab. No idea where to start or how to do this, and do not have scripting/programming knowledge.
I have attached two files. Original.xlsx which represents base file and the Modified.xlsx which represents what we would like post-process.
Thanks in advance for the assistance with this request.
Orignal.xlsx
Modified.xlsx
I have a workbook that has one main worksheet, named Data.
The data worksheet then has information that is in various Group, grouped by segment type eg MSH, EVN, PID, PD1 etc. We didn't create the base document, it was provided by vendor for use.
We need to retain the Data tab and all of its information in current state. The ask is to create additional worksheets for each group. And for each new worksheet created it should keep the column headers in row-1 of the Data tab. No idea where to start or how to do this, and do not have scripting/programming knowledge.
I have attached two files. Original.xlsx which represents base file and the Modified.xlsx which represents what we would like post-process.
Thanks in advance for the assistance with this request.
Orignal.xlsx
Modified.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
sktneer, thank you again. I was able to locate how to turn on the developer in Excel 2010, and do now see the VBA.
To implement this code to another workbook, follow these steps...
To implement the code:
1) Open your workbook.
2) Press Alt+F11 to open VBA Editor.
3) On VBA Editor --> Insert --> Module. This will open a new code window.
4) Paste the code on the opened code window.
5) Close VBA Editor.
6) Save your workbook as macro enabled workbook.
How to Run this code:
Press Alt+F8 to open Macro window --> Select the macro from list --> Click on Run.
Alternatively....
1) Insert a shape on the worksheet --. Right click on Shape --> Assign Macro --> Select the macro from the available macro list --> Ok.
2) On Developer Tab --> Insert --> Insert a Button (Form Control) --> Assign the macro when prompted.
To implement the code:
1) Open your workbook.
2) Press Alt+F11 to open VBA Editor.
3) On VBA Editor --> Insert --> Module. This will open a new code window.
4) Paste the code on the opened code window.
5) Close VBA Editor.
6) Save your workbook as macro enabled workbook.
How to Run this code:
Press Alt+F8 to open Macro window --> Select the macro from list --> Click on Run.
Alternatively....
1) Insert a shape on the worksheet --. Right click on Shape --> Assign Macro --> Select the macro from the available macro list --> Ok.
2) On Developer Tab --> Insert --> Insert a Button (Form Control) --> Assign the macro when prompted.
ASKER
Works great. Expected outcome produced.
You're welcome. Thanks for the feedback.
ASKER
Can you tell me how I would put this code/ where I would put this code on other workbooks that are laid out in similar fashion? In that the other workbooks are also columns A:E but the segments(groups) are different.
Again thank you for this solution.