Copy a row from one worksheet to another based on color filling of a cell in the row

Please, I need help with copying a row from one worksheet to another based on the color filling of a cell on the row, and ensuring that subsequent copying of rows will not duplicate rows that have been previously copied.

I’ll try and explain the problem that I have:

1. I have used Excel conditional formatting feature to assign three different colour fills to cells in a particular column based on the figure that appears in each of the cells in that column. If any cell in that column does not meet any one of the three criteria, the cell will not be filled with a colour. It will be left blank. This bit works OK.

2. Given that there are three different colours, I created a sheet for each of the three colours, e.g. Red, Green, Yellow. I also created another sheet named ‘All Colours’.

3.So in effect, I have 5 worksheets – Main Data, Red, Green, Yellow, All Colours.

4.When for a cell meets the criterion for e.g. a Green colour, and the cell is filled with Green, then all the data on that row must be copied to the first available empty row in the ‘Green Worksheet. The same row is also copied to the first available row in the ‘All Colour’ worksheet.

5.Same for Red and Yellow.

6.So for each cell that meets the colour criterion, the entire row will be copied to the next blank row of both the relevant colour worksheet and also to the ‘All Colour’ worksheet. (two copying will be triggered for each colour criterion that is met.

7.The code that I have tried to put together (see below) does OK with the initial copying from the Main Data – copying to both the particular colour worksheet and the ‘All Colour’ worksheet, respectively.

8.I need to add that when I tested the code in a sample workbook, I used three texts to represent each of the colours, and used those as ‘Range Values’ in the vba code as follows: “YES” for Yellow, “NO” for Green, and “DU” for Red (see the vba code below). I used these to represent each colour because I do not know how to add the colour codes as the Range Values in the vba code.

9.I know that the colour codes for the three colours are – Yellow is RG(255, 255, 0); Green = RGB(0, 176, 80), and Red = RGB(255, 0, 0). I would need help also to adjust Range Values with the above correct colour codes, please.

10.Another major difficulty that I have is that, in the course of testing the vba code, I observed that when a user adds to the Main Data sheet additional rows that contain cells which meet the criteria, and the copy vba code is triggered, it will copy across the new rows and also the other rows that had been previously copied across – in effect, duplicating previously-copied rows each time the code is triggered.

Please Login or Register  to view this content.

Please, I need help with adjustment to the code, to ensure that after the initial copying is triggered, then subsequent copying will not copy across, again, the rows in the Main Data sheet that had been previously copied .

What I need is that upon each trigger of the code, the rows to be copied across to the next blank row of either ‘Red sheet’, ‘Green sheet’, ‘Yellow Sheet’ AND ‘All Colour sheet’ should only be those rows with new cells that have met the colour criteria.

Thanks all in anticipation of your kind help.

Buddy8

Thanks Xladept for your kind response. I am sorry that the thread is long. It was all aimed at explaining things fully so I can get the help that I need.

The summary is that there are two things that I need help with regarding the vba code that I provided:

1. The code is supposed to copy across rows that have either Red, Green or Yellow colour fills in their cells in column E. I need help with replacing each of the following 3 lines in the code:

Please Login or Register  to view this content.

For e.g.,
replace the 'YES' in (a) with the colour code for Yellow which is RGB (255, 255, 0)

replace the 'NO' in (b) with the colour code for Green which is RGB(0, 176, 80), and

replace the 'DU' in (c) with the colour code for Red which is RGB(255, 0, 0).

2. Finally, after the initial copying across, when a user creates a new row and makes additional entry in the Main Data sheet, and that entry meets the colour fill criteria, the code should copy across ONLY that row. It should be prevented from copying across the rows that have been previously copied across.

I hope that this summary is helpful.

Thank you.

Buddy8

Hi Kenny,

The PM I sent before was a mistake (I pressed the wrong button, and it sent your original message to me back to you as a response).

The code is supposed to copy across rows that have either Red, Green or Yellow colour fills in their cells in column E. I need help with replacing each of the following 3 lines in the code...

The Red, Green, Yellow are probably produced by CONDITIONAL FORMATTING. Unfortunately, VBA CAN NOT identifiy CONDITIONAL FORMATTING colours. In order for VBA to identify which item is supposed to be Red, Green, or Yellow, VBA will have to use the CONDITIONAL FORMATTING rules that produced the colours.

--------------------
If you were able to use the Colours, the syntax would be:

Please Login or Register  to view this content.

Since we don't know the Conditional Formatting rules, you will have to upload a sample workbook that contains the Conditional Formatting to obtain additional help.

Lewis

Dear Lewis

Thanks too for your kind response. I have inserted the ‘If Range ..” line you suggested, with the appropriate colour numbers, but I could not get the vba code to work.

As you suggested, I have attached a sample dummy workbook with the Main Data, Green, Yellow, Red and Summary worksheets, respectively.

It also contains the vba code which I have put together.

In the Main Data sheet, I have used Excel conditional formatting to insert the the colour fills in column E. If you make an entry into any of the rows (columns A to C) and add a number into the D column, the appropriate colour fill will appear in the E column as follows:
1 to 11 = Red
13 to 29 = Yellow
30 to 41 = Green

1.I’d like the vba code to look at column E and copy the row for each coloured cell to the first available blank row in the appropriate colour worksheet.

2.Each of such row should be copied both to the appropriate colour worksheet and also to the Summary sheet (as the vba code attached to the workbook shows)

3.The vba code should please be tweaked to effect the copying of the existing rows with coloured cells i.e. (rows 2 to 12).

4.I also need help to prevent the duplication of previously copied rows when subsequent entries are made in the Main Data sheet.

5.For example, after the code has acted on rows 2 to 12 , if you put in a dummy entry in row 13 (columns A to C) and add say the number ‘20’ in column D – the Yellow colour will appear in E column. Do same for Row 14 and add say the number ‘13’ to its column D – the Green colour will appear in E column.

6.At this stage, when the code is triggered, only Rows 13 and 14 should be copied to the first available blank rows in both their appropriate colour sheets and the Summary sheet.

I'll be very grateful for any help.

Thanks all.

Buddy8

Hi Xladept,

Thanks very much for your code and for the time you’ve put in to do this for me. I am grateful.

I tried your code. It is almost there. There are two issues that we should, please, correct and the code will work perfectly OK the way that I want.

1. I observe that if a number appears more than once in column D, and even though the row contains different Data or Records, the rows will not copy across. I labelled column D with the title ‘ID’ which appears to make the contents unique. I know that in a normal circumstance ‘ID’ should be unique and cannot be duplicated. I think I used the wrong title for the column, hence the implication of the contents. This should not be the case. May be I should change the title of that column to ‘RESULT’..

2. What it means is that a number for e.g. 15, can appear many times in column D with the resulting colour being ‘Yellow’, the entire row should be copied across to both its relevant colour sheet & also to the Summary Sheet.

3. I have attached another file which shows this. If you look at the Main Data sheet of this new file attached, to test it, I have added data in rows 13 to 17.

4) Row 13 column D contains the number 15 giving rise to Yellow. This row was not copied across because the code appears to regard the ‘15’ row 13 as a duplicate of the ‘15’ in row 7, even though the rows contain different data/record. The same with Row 17 and Row 4. They both have the number ‘31’ in column D.. Even though they both accurately show ‘Green’ and have different data/records, the code did not copy Row 17 across.

5) Both Rows 13 and 17 should also be copied both to their respective colour sheets and also to the ‘Summary’ sheet

For the ‘SUMMARY SHEET’:
Finally, I also observe that everything in the Main Data Sheet is copied across to the Summary Sheet. Please, this should not be the case. Only rows with any of the THREE colours should be transferred both to their respective colour sheets and also to the Summary Sheet.

Any row without any of those colour should not be left out and not copied across even to the Summary sheet, e.g. Rows 5, 9, and 15 (of the file currently attached.)

That was a very brilliant code that you wrote. However, with these two adjustments, the code will be working perfectly OK for me.

Thanks so much for everything.

Buddy8

Hi Xladept,

After sending my Post #7 and after my PM to you, something occured to me. If we must use a column as an ID column to ensure that there is no duplication of rows, then I will be happy to insert a column at the beginning of each sheet and then just number the rows 1,2,3,4,5, etc. down. and have these numbering serve as the ID or Key (that is, if in using your code, we must have a unique ID column).

But if your code can be adjusted to copy across a row with any of the colours without such additional ID column and notwithstanding that the row has, in column D, a number that has occured previously, then I will be very happy. I am not particularly worried about the duplication of an entire row. Such an occurence is not usually possible with the work that we do.

In that case, in the Sample-2 file that I attached to my Post #7, you could insert a first column on each sheet (named .Row No.') and then give the column a normal 1,2,3 ... numbering.

In tweaking your code, I hope you will also remember please, that if a row, in the Main Data sheet, does not have any Red, Green or Yellow colour, it should not be copied to the Summary sheet.

Again, thanks for your help.

Buddy8

Hi LJMetzger,

That was quite some work by you. Thanks for your time and help.

I have tried your code and observe that it works exactly like Xladept's code in his Post #6. The only difference is that Xladept's code ignores or leaves out a row that contains, in column D, a number that has previously occured in that column - even though the rows contain different other data/record. Such rows should be copied across provided they meet the criteria and contain Red, Green, or Yellow colour in the adjacent cell in column E.

As one who is still new in vba, I currently feel overwhelmed by your massive code. I want to learn and improve on my knowledge and skills like you all. Your code is very brilliant, and I will want to use it as a learning resource. It will help me a lot.

However, for the work that I am currently doing, I find a smaller code, like Xladept's, easier to work with and manage. Additionally, we have other users of the workbook, and I'd like to keep things very simple for everyone.

If Xladept's code could be tweaked along the lines of my Posts #7 and #9, respectively, I have not doubt that it will meet my needs. I'll appreciate if you could also have a look at them. I'll appreciate your kind input to it, if possible, to see how that particular little code could be tweaked so that -

a) it can pick up and copy across rows that have any one of those three colours - even though they have, in column D, numbers that exist previously in that column; and

b) where a row, in the Main Data sheet, does not have either Red, Green or Yellow, it does not get copied to the Summary Sheet.

Again, thanks for your kind help.

Buddy8

Hi Xladept,

Thanks very much for your prompt and kind response, also for the altered code and the event code for the Main Data sheet. I believe you want me to copy the event code onto the Main Data sheet module.

I will try that out.

I remain eternally grateful to you for your patience and kind help.

Kind Regards.

Kenny

Hi Xladept,

Thanks very much for being able to help further with this.

I have attached the file as you advised.

1. The destination sheets (Green, Yellow, Red), are the first three sheets in the workbook.

2. The 10 Main Data sheets (which in the live workbook will have different names) will then come after. For this test file, I have given the 10 worksheets the names Jan, Feb, Mar ..to Oct.

3. In this example, the critical column is Column G – whereas in the previous example, it was column D.

4. I have populated each of the 10 worksheets with test data.

5. The code should check column G of each of the 10 worksheets, and

a) any cell in that column that has the numbers 1 to 12 (& showing Red colour), the entire corresponding row should be copied to the first blank row in the ‘RED’ worksheet.

b) Any cell in that column that has the numbers 13 to 29 (and showing Yellow colour), the entire corresponding row should be copied to the first blank row in the ‘YELLOW’ worksheet, and

c) Any cell in that column that has the numbers 30 to 41 (and showing Green colour), the entire corresponding row should be copied to the first blank row in the ‘GREEN’ worksheet.
d) Any cell in that column that has any number above 41 will not be coloured; it will be blank. The corresponding row for such a cell should not be copied.

6. The code should do (a) to (d) above for each of the 10 worksheets.

7. And as in the previous code, when subsequent entries are added to any of the 10 sheets, the previously copied rows should not be copied or duplicated, please. Only the newly or subsequently added rows should be copied across.

8. When subsequent entries are added, it could be in either one, two, three or any number of the 10 worksheets. However, such entries can only be done by a user on a one-sheet-after-a-time basis.

9. You will observe that the number that appears in the critical column G of each of the 10 main data worksheets is a product of column E x column F (e.g. E2*F2) etc.

10. Therefore, with respect to the EVENT code, this means that, if it is possible, whenever any figure appears in any cell in column G of any of the 10 worksheets, the main code will be triggered.

You advised that I should use the 'Paper clip' icon to upload the file. I could not find that particular icon, so I had to upload the file the usual way that I have in the past. I hope it is OK.

Please let me know if you need further information or clarification on this or if you want me to upload it again.

Thank you for your continued support.

Kenny

Hi Xladept,

I have just seen your latest codes for the new workbook arrangement. Thanks for your continued support. I will test it out and report back to you.

Meanwhile, in your Post, you indicated as follows: "The no duplicate requirement dictates that we do the whole thing every time" May you kindly clarify what you mean that I should 'do everytime'. I'll appreciate your kind clarification and direction so that I don't make a mistake or mess the whole thing up.

Kind Regards.

Kenny

Hi Xladept,

Thanks. The procedure you have described is actually what I wanted, i.e. each time an entry is made in any one of the 'month' sheets, the code will trigger automatically and update the relevant 'colour' sheet.

If, for e.g. four entries are made in the March sheet, the code will be executed/triggered after every one of the four entries. That's perfectly OK for me. I like it that way.

Thank you.

Kenny

Sorry post deleted.

No Xladept, there was no problem. I was trying to send a 'thank you' Post to the administrators of Excel Forum, for the opportunity they created to enable some of us to meet brilliant and helpful people like you through Excel Forum. After typing it, I realised that sending it from this thread would send the Post to my email address!

When I found out that I needed to send it by clicking 'Contact Us' below, I deleted it but did not, and still do not know, how to remove the remnant completely from the thread. Glad though that I eventually contacted them through 'Contact Us' below.

Thanks heaps for caring and checking to know if I had any problem.

Kenny