Link to home
Create AccountLog in
Avatar of Andreas Hermle
Andreas HermleFlag for Germany

asked on

Split worksheets based on fill color using VBA

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

Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland image

The workbook is locked for editing.
Avatar of Andreas Hermle


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 ...
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.
Your Master sheet isn't very well designed to use as a Database and as a basis for splitting the data.
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
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.
Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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
It's actually a Slicer based on the Table, but works as a slicer from a PivotTable
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
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?
The sets column has the RGB value =  RGB 217, 217, 217

Thank you very much in advance.

Regards, Andreas
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.
Hi Roy, ok you got me convinced :-)

thank you very much for your superb help. Regards, Andreas
Thank you very much for your professional help. Regards, Andreas
Pleased to help. Remember that VBA is useful but if Excel has it's own inbuilt methods then use them.