happydonut
Board Regular
- Joined
- Nov 28, 2019
- Messages
- 57
- Office Version
- 365
- Platform
- 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):
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