We help IT Professionals succeed at work.

split worksheet into new worksheets based on changing value in Column A (by means of an Excel VBA)

240 Views
Last Modified: 2017-03-18
Dear Experts:

on the current worksheet I would like to split the current worksheet named 'DataSource' into several worksheets based on the value in Column A,
... ie. whenever the Value in Column A changes, all the rows that belong to this value are to be copied into a new worksheet with the respective value as worksheet name:


Basis (Worksheet named DataSource):
Column A     column B        Column C         Column D
EN-23          House                   New               370 Dollars
EN-23          House                   old                  450 Dollars
EN-23          Apartment           new                380 Dollars
EN-45          House                  old                    220 Dollars
EN-45          House                  new                    93 Dollars
EN-17          HOuse                  old                     84 Dollars
EN-17           HOuse                 new                  30 Dollars


After running the macro:

new worksheet named EN-23 is created with the following contents:
Column A     column B        Column C         Column D
EN-23          House                   New               370 Dollars
EN-23          House                   old                  450 Dollars
EN-23          Apartment           new                380 Dollars


new worksheet named EN-45 is created with the following contents
EN-45          House                  old                    220 Dollars
EN-45          House                  new                    93 Dollars

new worksheet named EN-17 is created with the following contents
EN-17          HOuse                  old                     84 Dollars
EN-17           HOuse                 new                  30 Dollars


Help is very much appreciated. I have attached a sample file for your convenience.

Thank you very much in advance

Regards, Andreas
Split_Worksheet_based_ColumnA.xlsx
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2016
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Rob HensonFinance Analyst
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Andreas HermleTeam leader

Author

Commented:
Rgonzo: great code, works very nice, thank you very much for your superb help. :-)

Rob Henson: Rob, this is very interesting, will test it this evening and then let you know. Thank you very much :-)
Andreas HermleTeam leader

Author

Commented:
Dear both: great jobs from both of you. I now have two approaches for these kind of tasks. That's really great. Rob: very interesting feature, this pivot approach. Thank you very much for it. Since I was asking for a VBA solution, the majority of the points go to Rgonzo. I hope you understand this.

Have a nice weekend. Regards, Andreas