Copy rows if cell a is green in any sheet to a master sheet ! Vba

[krsegz]

I am trying to do something relatively simple and can't seem to get the copied rows to show up. I would SINCERELY appreciate ANY HELP!!!!

1. I have 10 sheets in a workbook (I inserted all of these based off of the same macro enabled template)
2. I am trying to copy the highest priority data from each of the 9 worksheets into the 10th MASTER SHEET
3. For each of the 9 worksheets I have manually filled column A green (RGB 0,176,80) if it fits the criteria for highest priority
4. I would like to copy all rows with column A green to the MASTER SHEET
5. I would also like to change the fill color in the other 9 sheets to lime green after they have been copied into the MASTER SHEET

*I will also need to consistently add new data to the 9 other sheets and run the macro consistently to keep up with the new data that I fill green and designate as highest priority

i.e. 1 of the 9 sheets is called "MemorialW"
sheet 10 or the MASTER SHEET is called "MASTER PROSPECTS"


I have tried so many different ways to achieve this goal and have come closest with this:

Sub copybased_on_cell_interior_rgb()

Const green As String = "R:0 G:176 B:80" 'RGB(0, 176, 80)
Dim i As Long, FBlnkRow As Long

FBlnkRow = Worksheets("MASTERprospects").Range("A" & Rows.Count).End(xlUp).Offset(1).Row

With Worksheets("MemorialW")
For i = 2 To .Range("A" & .Rows.Count).End(xlUp).Row
Select Case rgb_valz(.Range("A" & i))
Case green
.Range("A" & i & ":V" & i).Copy Worksheets("MASTERprospects").Range("A" & Rows.Count).End(xlUp).Offset(1)
.Range("A" & i & ":V" & i).Interior.Color = RGB(0, 176, 80)
End Select
Next i
End With
End Sub


Public Function rgb_valz(rng As Range) As String
'Credits: snb
rgb_valz = _
"R:" & rng.Interior.Color Mod 256 & _
" G:" & (rng.Interior.Color Mod 256 ^ 2) \ 256 & _
" B:" & rng.Interior.Color \ 256 ^ 2
End Function


THANK YOU!!!!

 

[DanteAmor]

Try this:

Sub Macro3()
'
    Dim Sh As Worksheet, shM As Worksheet
    Dim lastS As Double, lastM As Double
        
    Application.ScreenUpdating = False


    Set shM = Sheets("Master")
    
    For Each Sh In Sheets
        If Sh.Name <> shM.Name Then
            If Sh.AutoFilterMode Then Sh.AutoFilterMode = False
            lastS = Sh.Range("A" & Rows.Count).End(xlUp).Row
            
            Sh.Range("A1:A" & lastS).AutoFilter Field:=1, Criteria1:=RGB(0, 176, 80), Operator:=xlFilterCellColor
            lastS = Sh.Range("A" & Rows.Count).End(xlUp).Row
            If lastS > 1 Then
                lastM = shM.Range("A" & Rows.Count).End(xlUp).Row + 1
                Sh.Range("A2:V" & lastS).Copy shM.Cells(lastM, "A")
                Sh.Range("A2:V" & lastS).Interior.Color = RGB(0, 176, 80)
            End If
            If Sh.AutoFilterMode Then Sh.AutoFilterMode = False
        End If
    Next
    
    Application.ScreenUpdating = True
    
    MsgBox "Finish!"
End Sub

 

[krsegz]

THANK YOU FOR THE RESPONSE! This actually pulled any row from any sheet with any data into the "master" and the green cells stayed green in the master (for the ones i manually filled individually on each of the 9 sheets to designate high priority). When it pulled all entries from all of the sheets, it inserted them into the master but then also removed all of these entries from their respective sheets that were not highlighted green. The only rows that stayed in their respective sheets were the ones I highlighted manually and the entire rows became green.

I need only the rows highlighted in green (from the 9 sheets) copied to the master. I would still like them to remain in their initial sheet as well and turn green like they have now. But i need all of the nonhighlighted rows to stay in their respective sheets and not transfer to the master and not change color.

THANK YOU!!!

Try this:

Sub Macro3()
'
    Dim Sh As Worksheet, shM As Worksheet
    Dim lastS As Double, lastM As Double
        
    Application.ScreenUpdating = False


    Set shM = Sheets("Master")
    
    For Each Sh In Sheets
        If Sh.Name <> shM.Name Then
            If Sh.AutoFilterMode Then Sh.AutoFilterMode = False
            lastS = Sh.Range("A" & Rows.Count).End(xlUp).Row
            
            Sh.Range("A1:A" & lastS).AutoFilter Field:=1, Criteria1:=RGB(0, 176, 80), Operator:=xlFilterCellColor
            lastS = Sh.Range("A" & Rows.Count).End(xlUp).Row
            If lastS > 1 Then
                lastM = shM.Range("A" & Rows.Count).End(xlUp).Row + 1
                Sh.Range("A2:V" & lastS).Copy shM.Cells(lastM, "A")
                Sh.Range("A2:V" & lastS).Interior.Color = RGB(0, 176, 80)
            End If
            If Sh.AutoFilterMode Then Sh.AutoFilterMode = False
        End If
    Next
    
    Application.ScreenUpdating = True
    
    MsgBox "Finish!"
End Sub

 

[DanteAmor]

You could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

I did the test and only the cells in green were copied

If you can, in the file on sheet 11 put the expected result of a couple of sheets.