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

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
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
12,593
Office Version
  1. 2007
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,445
Messages
5,528,800
Members
409,835
Latest member
Mafu1267

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top