Copy YELLOW, Paste

mmmarty

Board Regular
Joined
Oct 23, 2004
Messages
79
Gday,

WkSheets 1..5, each sheet has data in ranges A1:Z100.

I manually browse all sheets and Highlight Yellow specific cells.
Is there a method of exporting the values in my YELLOW cells to Wksheet 6 in column A??

Thanks
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Banker1

Active Member
Joined
Mar 10, 2005
Messages
463
Which yellow are you using?? The yellow with color index 6 (the brighter yellow). Do you use color anywhere else in the sheet?

It may be easier, depending of your format, to copy any data that has a colored cell. That said, if you color your column headings or any other cells within the sheet that do not contain data you wish to copy this method won't work.
 

mmmarty

Board Regular
Joined
Oct 23, 2004
Messages
79
Yes It's the Bright Yellow, (6), but any one color would work for my purposes. Only cells with a value would need to be copied over to my 6th Wrksheet. There are no other colors in my Wksheets.
Any ideas if this is doable?
 

Banker1

Active Member
Joined
Mar 10, 2005
Messages
463
It's doable... wanted to clarify a few things before putting together the code. Should have something for you in a few.
 

Banker1

Active Member
Joined
Mar 10, 2005
Messages
463

ADVERTISEMENT

See example in later response
 

mmmarty

Board Regular
Joined
Oct 23, 2004
Messages
79
It's Close Banker, not quite giving the desired output.
To test,
Sheet1, one Yellow cell, ie value 55.
Sheet2, one Yellow Cell, ie value 44

Output on Sheet6 is 5 occurences of value 55 listed in Range A1:A5
and no value retrieved from Sheet2.

So Close I can taste it.
 

Banker1

Active Member
Joined
Mar 10, 2005
Messages
463

ADVERTISEMENT

Hang on... let me test your example.
 

Banker1

Active Member
Joined
Mar 10, 2005
Messages
463
It works for me... Sheet1 value 55 in cell D8 color yellow, Sheet2 value 44 cell B7 color yellow, nothing in Sheets 3 - 5.

Output value in Sheet6 cell A1 = 55, cell A2 = 44.

Here is the code again... disregard the code above

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> CopyYellow()
<SPAN style="color:#00007F">Dim</SPAN> WS <SPAN style="color:#00007F">As</SPAN> Worksheet
<SPAN style="color:#00007F">Dim</SPAN> WorkRange <SPAN style="color:#00007F">As</SPAN> Range

<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> WS <SPAN style="color:#00007F">In</SPAN> ThisWorkbook.Sheets
    <SPAN style="color:#00007F">If</SPAN> WS.CodeName <> "Sheet6" <SPAN style="color:#00007F">Then</SPAN>
        WS.Select
        <SPAN style="color:#00007F">Set</SPAN> WorkRange = Range("A1:Z100")
    <SPAN style="color:#00007F">Else</SPAN>
        <SPAN style="color:#00007F">GoTo</SPAN> End1
    End <SPAN style="color:#00007F">If</SPAN>
    
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> Cell <SPAN style="color:#00007F">In</SPAN> WorkRange
        <SPAN style="color:#00007F">If</SPAN> Cell.Interior.ColorIndex <> xlNone <SPAN style="color:#00007F">Then</SPAN>
            Cell.Copy
            <SPAN style="color:#00007F">If</SPAN> Sheet6.Range("A65536").End(xlUp).Value = "" <SPAN style="color:#00007F">Then</SPAN>
                Sheet6.Range("A65536").End(xlUp).PasteSpecial Paste:=xlPasteValues
            <SPAN style="color:#00007F">Else</SPAN>
                Sheet6.Range("A65536").<SPAN style="color:#00007F">End</SPAN>(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
        Application.CutCopyMode = <SPAN style="color:#00007F">False</SPAN>
        End <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> Cell
<SPAN style="color:#00007F">Next</SPAN> WS

End1:
End <SPAN style="color:#00007F">Sub</SPAN></FONT>
 

mmmarty

Board Regular
Joined
Oct 23, 2004
Messages
79
I used your second code posting - My output remains at 5 instances of whatever is highlight yellow in Sheet1 reported to Sheet6. Hmmm.
 

Banker1

Active Member
Joined
Mar 10, 2005
Messages
463
I copied the code into a new book and tested again and it works for me... Check your PM's.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,234
Messages
5,571,045
Members
412,359
Latest member
misstoffeepenny
Top