Copying cetrtain cells in a column?

[Beachson]

I have a column and I have set a cell color for certain scattered cells and I simply want to move a copy of everyone of those cells directly to it's left into an empty column...how?

 

[Beachson]

I did this on a copy and it only did it for the first like 5 cells that were blue and when I tried it on the master spread sheet it showed a debug window highlight the second line of the code

 

[Beachson]

Error 1004 method of range

 

[lenze]

Sub CopyColor()
Dim cl As Range
For Each cl In Range("$A$2:$A" & Cells(Rows.Count, "A").End(xlUp))
If cl.Interior.ColorIndex = 5 Then cl.Copy Cells(cl.Row, "B")
Next cl
End Sub

This will copy cells from Column "A" to column "B" Note the differences between the original which was copying "B" to "A"

If you are getting an error, you must have a typo or something wrong because the CODE work as written. Post YOUR code
lenze

 

[Beachson]

It's still giving me error 1004 method of range...I do want the cells from B to copy to A but for some reason it's not working I have copied your code excactly into a module pressed ALT+Q then gone to the spreadsheet and pressed ALT+F8 and clicked run and thats what it says

 

[lenze]

I don't know why. I just tested it by copying it to a module, inserting Data in B2:B100 and randomly colored cells Blue. I ran the code and the values WHERE copied to Column "A" along with the Formatting(Blue color). The 2nd also works, but only transfers the Value, not the color.

lenze

 

[Beachson]

It worked although is there a reason why it would not be doing it for all of them it only did it up till cell 93 this time and none past any reason why that would happen

 

[Beachson]

every time that it ends up working (which i dont know why ever other time I try after it shows the error report I have to redue the whole module and start fresh) it does it for a random number of them up to cell 60 up to cell 93 up to cell 8 why is that I want it for 4000 cells

 

[lenze]

You don't perchance have any merged cells?? VBA hates merged cells!!!. You can, however, change the code like this

Sub CopyColor()
Dim cl As Range
For Each cl In Selection
If cl.Interior.ColorIndex = 5 Then cl.Copy Cells(cl.Row, "A")
Next cl
End Sub

Now, you can select all of the cells of interest (or all of column "B" if you want) and run the code

lenze