We help IT Professionals succeed at work.

Split worksheets based on fill color using VBA

Andreas Hermle
Andreas Hermle asked
on
16 Comments
1 Solution
66 Views
Last Modified: 2017-04-02
Dear Experts:

I would like to split a worksheet, named 'MasterWorksheet' (lots of rows and 8 columns) into several worksheets based on row colors:

Whenever the row color in Column A changes to RGB 217, 217, 217 all the rows till the next occurrence of such a grey-filled row have to be copied into a new worksheet along with the three first header rows of the 'MasterSheet' . The names of the newly created worksheets is to be derived from the cell contents of the respective grey cell.

I have attached a sample file for your convenience.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas

Split_Worksheet_Based_on_Color.xlsx
Comment
Watch Question

View Solution Only

Roy CoxGroup Finance Manager
CERTIFIED EXPERT

Commented:
The workbook is locked for editing.
Andreas HermleTeam leader

Author

Commented:
Hi Roy,

thank you very much for your swift help. I am afraid to tell you that you inadvertently sent me an xlsx-file which cannot contain VBA code.

I hope you still have the code :-)

Thank you very much ...
Roy CoxGroup Finance Manager
CERTIFIED EXPERT

Commented:
Andreas, I haven't sent a workbook. When I download your example it says that it is locked for editing.

I've managed to open it now.
Roy CoxGroup Finance Manager
CERTIFIED EXPERT

Commented:
Your Master sheet isn't very well designed to use as a Database and as a basis for splitting the data.
Andreas HermleTeam leader

Author

Commented:
Upps, Roy,  I am sorry to have misread your post, I am such a dummy ...

Ok, what if there was just one header row in the master sheet (instead of three) . Would that make the difference?

Regards, Andreas
Roy CoxGroup Finance Manager
CERTIFIED EXPERT

Commented:
You need one header row and an extra column determining the sub sets. Set up like this I can't really see any need fo VBA.

I'll edit it and see what you think.
Group Finance Manager
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Andreas HermleTeam leader

Author

Commented:
Hi Roy,

oh, what I nice approach, this is a slicer from the pivot table options, am I right?

Thank you very much, Regards, Andreas
Roy CoxGroup Finance Manager
CERTIFIED EXPERT

Commented:
It's actually a Slicer based on the Table, but works as a slicer from a PivotTable
Andreas HermleTeam leader

Author

Commented:
Oh, this is really a great feature. I am really happy to get to know this. Thank you very much for it.

Nevertheless, I am just curious, would it be possible to meet my initial requirement, splitting the master sheet based on color fill using VBA?

Regards, Andreas
Roy CoxGroup Finance Manager
CERTIFIED EXPERT

Commented:
It would be possible with the data arranged as in the example, the easiest way would be to use the Sets column, how would the rows be coloured?
Andreas HermleTeam leader

Author

Commented:
The sets column has the RGB value =  RGB 217, 217, 217

Thank you very much in advance.

Regards, Andreas
Roy CoxGroup Finance Manager
CERTIFIED EXPERT

Commented:
Using VBA to capture the colour I get RGB  (244, 176, 132). To split by colour you would require different colours for each set., I really think it is unnecessary.
Andreas HermleTeam leader

Author

Commented:
Hi Roy, ok you got me convinced :-)

thank you very much for your superb help. Regards, Andreas
Andreas HermleTeam leader

Author

Commented:
Thank you very much for your professional help. Regards, Andreas
Roy CoxGroup Finance Manager
CERTIFIED EXPERT

Commented:
Pleased to help. Remember that VBA is useful but if Excel has it's own inbuilt methods then use them.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.

View Solution