Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Copying selected cells

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hello..
    I have a problem, Excel cant 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 dont 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

  2. #2
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,607
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  3. #3
    New Member
    Join Date
    Feb 2002
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Chippenham, UK
    Posts
    144
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  5. #5
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,607
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  6. #6
    New Member
    Join Date
    Feb 2002
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 cant make it work L


Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •