Copy and paste except color-filled cells

[danacreilly]

Hi,

I have a master sheet that contains mostly text, describing the characteristics of several different accounts. If an account has been terminated, the row has been filled with the color yellow. I want to copy the entire master sheet and paste only the active accounts in a sheet titled "active accounts". How can I write a macro that copies and pastes data, with criteria excluding filled cells? I want to be able to run the macro every time I update the master sheet...

Thank you!

[Mordred]

Hi danacreilly, there are a few ways to transfer data based on your requirements. However, it may be best if you upload a mock workbook with some mock data so that we can see how many rows and columns need to be moved.

Regards:

[Mordred]

Or, you can try this on a copy of your workbook to see if it works correctly:

Sub danacreilly()
    Dim LastRowNo As Long, LastRowNoShtAA As Long
    Dim FlRngCnt As Long, RwNo As Long
    Dim RngToChk As Range, RwRng As Range, FlRng As Range, ToRng As Range
    Dim cl As Variant
    LastRowNo = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
    Set RngToChk = Sheets(1).Range(Sheets(1).Cells(1, 1), Sheets(1).Cells(LastRowNo, 1))
    For Each cl In RngToChk
        If cl.Interior.ColorIndex = xlNone Then
            RwNo = cl.Row
            Set RwRng = Sheets(1).Range("IV" & RwNo).End(xlToLeft)
            Set FlRng = Sheets("Sheet1").Range(cl, RwRng)
            FlRngCnt = FlRng.Cells.Count
            With Sheets(2)  'Sheets("ActiveAccounts")
                LastRowNoShtAA = .Cells(Rows.Count, 1).End(xlUp).Row + 1
                Set ToRng = .Cells(LastRowNoShtAA, 1).Resize(, FlRngCnt)
                ToRng.Cells.Value = FlRng.Cells.Value
            End With
        End If
    Next cl
End Sub

[danacreilly]

There is something wrong with the range in the code you gave me, so here is an example spreadhseet. In my actual master sheet there are 500 rows, and it will be updated quarterly.

Thank you so much again for your help!

[MarvinP]

Hi danacreily and welcome to the forum,

Code and VBA is hard to understand and work with unless you are a programmer. How about an easier method to get rid of all those yellow rows of data. Try this:

1. Click anywhere in your data (I'm assuming you have no blank rows in it)
2. Click the Sort & Filter Icon and then on the Filter submenu.
3. This will select your entire table
4. Now click on the Find & Replace Icon and on Replace
5. Click the Options >> button and then on the Format.. v to the right of Find What
6. Click the Fill tab and click on the same color you use to mark the deleted accounts (yellow) and OK
7. Now click "Replace All" at the bottom of the Find and Replace Dialog
8. Now back in Row 1 sort your data by Manager (or any other non blank column) and all yellow rows go to the bottom.
9. Simply copy and Paste all non blank rows (above the yellow ones at the bottom) to sheet 2.

I hope you can follow these steps.

Summary - Search and replace all yellow formatted cells with blank. Sort your data so the blanks fo to the bottom and then copy and paste cells that are left to Active Accounts sheet.

[danacreilly]

I do know how to copy and paste the cells manually, I was just hoping to find a code that will allow me to automatically update the active accounts sheet.

Thank you for responding Marvin P!

[MarvinP]

Hi,

What my method does is removes data in the yellow cells and puts them at the bottom of th table. That way you can do a single block copy and paste to the new sheet. VBA and code is overkill for this problem.