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

krsegz

New Member
Joined
Feb 28, 2019
Messages
2
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!!!!:confused:

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!!!!
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
14,835
Office Version
  1. 2010
Platform
  1. Windows
Try this:

Code:
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

New Member
Joined
Feb 28, 2019
Messages
2
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:

Code:
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

Well-known Member
Joined
Dec 3, 2018
Messages
14,835
Office Version
  1. 2010
Platform
  1. Windows
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.
 
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,651
Messages
5,838,579
Members
430,557
Latest member
MK15

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