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
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,553
Messages
5,838,075
Members
430,527
Latest member
MyFace2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top