PDA

View Full Version : Macro to Find Cells by Color and Copy to Different Worksheet



enigmatik
07-08-2016, 10:55 AM
Excel Version: 2016
Error: Run Time Error 424

Hello All,

Please forgive me for being very new to VBA. I am trying to select cells in a particular column by color, and copy both the color coded cell and the cell directly above it into a different worksheet. I would like for the color coded cell to be in one column and the cell from the row above to be in the next column. I know that the line of code telling excel to copy is completely wrong. Any advice i could get would be greatly appreciated!


Sub CopyColor() Dim rCell As Range
ActiveSheet.Name = "RawData"
Sheets.Add.Name = "Results"
Sheets("RawData").Activate
For Each rCell In ActiveSheet.UsedRange
If rCell.Interior.ColorIndex = -4142 Then
rCell.Offset(-1, 0).Resize(2, 1).Copy Destination:=Results.Range(A).End(xlUp)(2, 1)
End If
Next rCell

End Sub

jolivanes
07-08-2016, 03:06 PM
This

Destination:=Results.Range
needs to be

Destination:=Sheets("Results").Range
but what is this meant to do

Range(A).End(xlUp)(2, 1)
That does not work but we need to know where you want it pasted.
First empty cell in Column A? If so, use

Destination:=Sheets("Results").Cells(Rows.Count, "A").End(xlUp).Offset(1)