Copy and paste entire rows based on fill color

MrsHale 

You can automate the process of copying entire rows based on fill color in Excel using VBA (Visual Basic for Applications). Here's a step-by-step guide to create a VBA macro to copy rows based on their fill color to different worksheets:

Important: Before using VBA, make sure you save your Excel workbook as a macro-enabled file with the extension ".xlsm."

1. Press Alt + F11 to open the Visual Basic for Applications editor.
2. In the editor, click Insert > Module to insert a new module.
3. Copy and paste the following VBA code into the module:

Vba code not tested:

Sub CopyRowsByColor()
    Dim wsSource As Worksheet
    Dim wsYellow As Worksheet
    Dim wsBlue As Worksheet
    Dim wsNoColor As Worksheet
    Dim ws As Worksheet
    Dim lRow As Long
    Dim cellColor As Long
    Dim TargetRow As Long
    
    ' Set your source worksheet
    Set wsSource = ThisWorkbook.Sheets("Master")
    
    ' Create or reference destination worksheets
    On Error Resume Next
    Set wsYellow = ThisWorkbook.Sheets("Yellow")
    Set wsBlue = ThisWorkbook.Sheets("Blue")
    Set wsNoColor = ThisWorkbook.Sheets("NoColor")
    On Error GoTo 0
    
    If wsYellow Is Nothing Then
        Set wsYellow = ThisWorkbook.Sheets.Add
        wsYellow.Name = "Yellow"
    End If
    
    If wsBlue Is Nothing Then
        Set wsBlue = ThisWorkbook.Sheets.Add
        wsBlue.Name = "Blue"
    End If
    
    If wsNoColor Is Nothing Then
        Set wsNoColor = ThisWorkbook.Sheets.Add
        wsNoColor.Name = "NoColor"
    End If
    
    ' Loop through rows in the source worksheet
    For lRow = 2 To wsSource.Cells(Rows.Count, 1).End(xlUp).Row
        cellColor = wsSource.Cells(lRow, 1).Interior.Color
        
        ' Choose the target worksheet based on cell color
        Select Case cellColor
            Case RGB(255, 255, 0): Set ws = wsYellow
            Case RGB(0, 0, 255): Set ws = wsBlue
            Case Else: Set ws = wsNoColor
        End Select
        
        ' Find the last row in the target worksheet
        TargetRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1
        
        ' Copy the entire row to the target worksheet
        wsSource.Cells(lRow, 1).EntireRow.Copy ws.Cells(TargetRow, 1)
    Next lRow
End Sub

1. Modify the code as needed. In the code, replace "Master", "Yellow", "Blue", "NoColor", and the RGB color values with your sheet names and color codes.
2. Close the VBA editor.
3. To run the macro, press Alt + F8, select CopyRowsByColor, and click "Run."

The macro will copy rows with different fill colors to the specified worksheets. You can run the macro whenever you need to update the destination sheets based on the colors in your master sheet. The text, steps and Code was created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.