Copying selected cells

[Stono]

Hello..
I have a problem, Excel can’t seem to understand me, I would like to copy
Cells (I,”B”) And Cells (I,”C”) and Cells (I,”M”) from one worksheet to another
It gives me a type miss match error!!! I am choosing these cells off the value in column “M”.
Unless I say that entire rows. Copy. And I don’t want that ..
Also I would like them to be all in the same row in the secound sheet

This is my code that works

Sub Copy()

Dim RngDestination As Range
Dim i As Integer
Set RngDestination = Worksheets("Sheet2").Range("A1")
For i = Cells(Rows.Count, "M").End(xlUp).Row To 1 Step -1
If Cells(i, "M").Interior.ColorIndex = -4142 Then
Cells(i, "M").EntireRow.Copy Destination:=RngDestination
Set RngDestination = RngDestination.Offset(RngDestination.Rows.Count)
End If
Next i


And this is what I am trying to do

Sub Copy2()
Dim RngDestination As Range
Dim i As Integer
Set RngDestination = Worksheets("Sheet2").Range("A1")
For i = Cells(Rows.Count, "M").End(xlUp).Row To 1 Step -1
If Cells(i, "M").Interior.ColorIndex = -4142 Then
Cells((i, "B"),(i,"C")Cells(i,"M")).Copy Destination:=RngDestination
Set RngDestination = RngDestination.Offset(RngDestination.Rows.Count)
End If
Next I

Thanks for the help

[Russell Hauf]

Change your "M" to 13 - which is the corresponding column number for column M (and "B" to 2, and "C" to 3, etc.).

-rh

[ This Message was edited by: Russell Hauf on 2002-02-28 09:19 ]

[Stono]

it is still giving me the same error
this what i did
Cells((i, 2),(i,3)Cells(i,13)).Copy Destination:=RngDestination

syntax error...
please advice
thanks

[gplhl]

On 2002-02-28 11:11, Stono wrote:
it is still giving me the same error
this what i did
Cells((i, 2),(i,3)Cells(i,13)).Copy Destination:=RngDestination

syntax error...
please advice
thanks

Cells((i&"2"),(i&"3")Cells(i&"13")).Copy

Try That,


_________________
Regards,

Gary Hewitt-Long

[ This Message was edited by: gplhl on 2002-02-28 11:57 ]

[Russell Hauf]

On 2002-02-28 11:11, Stono wrote:
it is still giving me the same error
this what i did
Cells((i, 2),(i,3)Cells(i,13)).Copy Destination:=RngDestination

syntax error...
please advice
thanks

I didn't look at your second sub very closely. Exactly what cells are you trying to copy? Cells only works with one row and one column, separated by commas.

[Stono]

Thank you both for your responses

The cells that I am trying to copy are Cell (I,”B”) And Cell (I,”C”) And Cell (I,”M”) .
where I is an Integer (Row Number)
That means three cells in one row ….

And till now I can’t make it work L