Copy YELLOW, Paste

[mmmarty]

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

 

[Banker1]

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]

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]

It's doable... wanted to clarify a few things before putting together the code. Should have something for you in a few.

 

[Banker1]

ADVERTISEMENT

See example in later response

 

[mmmarty]

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]

ADVERTISEMENT

Hang on... let me test your example.

 

[Banker1]

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]

I used your second code posting - My output remains at 5 instances of whatever is highlight yellow in Sheet1 reported to Sheet6. Hmmm.

 

[Banker1]

I copied the code into a new book and tested again and it works for me... Check your PM's.