Results 1 to 4 of 4
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Move row(s) based on cell (2003)

    I have a sheet from which I'd like to move entire row(s) where the related A column contains a 1 and leave the other rows.
    I would like two macro options, actually (I think this is a macro issue)...one to move the row(s) to another (blank, next) sheet in the same workbook and another to move the row(s) to another workbook. If the original workbook is called "DATA" then I'd move the row(s) to one called "DATA1" (already created and open) and place them in the first sheet in that workbook. ("above" any other rows in that sheet).

    I hope this makes sense.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Move row(s) based on cell (2003)

    When a row is moved to another sheet, do you want the rows below it to move up?

    What do you mean by ("above" any other rows in that sheet)? That existing rows should be moved down, and the new row placed in row 1?

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Re: Move row(s) based on cell (2003)

    1. Yes, I would want the rows moved and then the gaps closed...i.e., move all the other rows "up"
    2. Yes, the existing rows, if any, in the second sheet should be moved "down" and this new row or rows started in row 1 of the second sheet.

    Thanks for quick reply, as usual!

    Kevin

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Move row(s) based on cell (2003)

    The following macro will move rows with 1 in column A from Sheet1 to Sheet2:

    Sub MoveRows()
    Dim wshSource As Worksheet
    Dim wshTarget As Worksheet
    Dim r As Long
    Dim m As Long

    Set wshSource = Worksheets("Sheet1")
    Set wshTarget = Worksheets("Sheet2")

    m = wshSource.Cells(wshSource.Rows.Count, 1).End(xlUp).Row
    For r = m To 1 Step -1
    If wshSource.Cells(r, 1) = 1 Then
    wshSource.Rows®.Copy
    wshTarget.Rows(1).Insert
    wshSource.Rows®.Delete
    End If
    Next r
    End Sub

    If you want to move to a sheet in another workbook, change the line

    Set wshTarget = Worksheets("Sheet2")

    to

    Set wshTarget = Workbooks("Otherworkbook.xls").Worksheets("Sheet2" )

Posting Permissions

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