Select/copy all colored rows

happydonut

New Member
Joined
Nov 28, 2019
Messages
34
Office Version
  1. 365
Platform
  1. Windows
Hello,

I have a worksheets with lots of columns and rows. The number of rows varies depending on which file I open. The amount of columns is fixed (A to AC).
Of all these rows (starting from row 2), only some are relevant for me, which is why I fill them with colors.

What I would like is a macro that copies all the colored rows (from row 2 to the last row) and paste the colored rows to a new sheet.

Anyone has suggestion?

I used the Record Macro for some help, and added something myself as well, this is what I got so far (however, it only copies yellow colored rows):


VBA Code:
Sub CFilter()
'
' CFilter Macro
'
' Keyboard Shortcut: Ctrl+m
'
   
LastRow = Cells(Rows.Count, "A").End(xlUp).Row

    ActiveSheet.Range("$A$1:$AC$1").AutoFilter Field:=1, Criteria1:=RGB(255, _
        255, 0), Operator:=xlFilterCellColor
    Range("A1:AC" & LastRow).Select
    Selection.Copy
    Sheets.Add After:=ActiveSheet
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A1").Select
End Sub
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

happydonut

New Member
Joined
Nov 28, 2019
Messages
34
Office Version
  1. 365
Platform
  1. Windows
Also, another question:
If some colored rows have been filtered out and I start the macro, can I somehow include them to be copied as well (if not removing the filter)?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,383
Messages
5,635,951
Members
416,889
Latest member
dhegs

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