Link to home
Start Free TrialLog in
Avatar of Andreas Hermle
Andreas HermleFlag for Germany

asked on

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

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
ASKER CERTIFIED SOLUTION
Avatar of Rgonzo1971
Rgonzo1971

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Andreas Hermle

ASKER

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 :-)
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