Thread: Copy and paste except color-filled cells

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:

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

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.

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.