Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: VB Code to move data from one sheet to another

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    England
    Posts
    212
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Please can someone help me with the following scenario. I want to look through column B in Sheet 1 and where the word "complete" is present in column B, I want to remove the entire row and paste it into sheet 2, starting at the first blank row. Any suggestions for the required code would be greatly appreciated.

    Thanks

    Matt

    [ This Message was edited by: Matt on 2002-04-16 11:23 ]

    [ This Message was edited by: Matt on 2002-04-16 11:38 ]

  2. #2
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Matt,

    This code worked for me. Let us know if it works for you.

    ---begin VBA---
    Sub tester()
    Dim rng As Range, usedcell As Range
    Dim lastrow As Long, x As Boolean

    Set rng = Intersect(Sheets("Sheet1").UsedRange, _
    Sheets("Sheet1").Range("B:B"))

    For Each usedcell In rng
    x = Evaluate("=NOT(ISERROR(SEARCH(""COMPLETE""," & usedcell.Address & ",1)))")
    If x Then
    lastrow = Sheets("Sheet2").Cells(Rows.Count, "B").End(xlUp).Row
    Sheets("Sheet1").Rows(usedcell.Row).Copy Sheets("Sheet2").Rows(lastrow + 1)
    End If
    Next usedcell

    End Sub
    ---end VBA---

    HTH,
    Jay

    EDIT: If you want to cut the data rather than copy it to the new sheet, change

    Sheets("Sheet1").Rows(usedcell.Row).Copy Sheets("Sheet2").Rows(lastrow + 1)

    to

    Sheets("Sheet1").Rows(usedcell.Row).Cut Sheets("Sheet2").Rows(lastrow + 1)


    [ This Message was edited by: Jay Petrulis on 2002-04-16 12:13 ]

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    England
    Posts
    212
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    That's great!Thanks for your help Jay

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
  •