none
Macro to search a sheet and cut pastre to another sheet based on cell data

    Question

  • hi All

    I have a workbook to track sick with 2 sheets, current amd resumed. when an employee resumes i change the cell in column H to resume.

    I need a macro that, when selected will search through all the rows and if resumed is found in column h it will select that row, copy the data and paste the values to the next available row on sheet "resumed" then delete the row and 'move up' on sheet "sick"

    Any help will be appreciated.

    Tom

    Monday, November 11, 2013 3:41 PM

Answers

  • Hi Tom,

    I won't write your whole function but you can start using the Range Find method:

    Dim rngSearch as range, rngMatch As Range

    Set rngSearch =ActiveSheet.Range("H1:H1000") Set rngMatch = rngSearch.Find("Resume", LookIn:=xlValues, lookat:=xlWhole) Debug.Print rngMatch.Row

    You will need to incorporate a loop and use .Findnext to iterate through all data.

    Each result can then be copied to the other sheet...


    Brian, ProcessIT- Hawke's Bay, New Zealand

    Monday, November 11, 2013 8:50 PM
  • Maybe something like this.

    Sub CopyData10()
    Dim Rng As Range, cell As Range
    Dim rw As Long
    Set Rng = Worksheets("Sick").Range("H1:H10")
    rw = 1
    For Each cell In Rng
    If LCase(cell.Value) = "x" Then
    Worksheets("Resumed").Cells(rw, "A") = cell.Offset(0, -1)
    rw = rw + 1
    End If
    Next
    End Sub


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Sunday, November 17, 2013 9:27 PM

All replies

  • Hi Tom,

    I won't write your whole function but you can start using the Range Find method:

    Dim rngSearch as range, rngMatch As Range

    Set rngSearch =ActiveSheet.Range("H1:H1000") Set rngMatch = rngSearch.Find("Resume", LookIn:=xlValues, lookat:=xlWhole) Debug.Print rngMatch.Row

    You will need to incorporate a loop and use .Findnext to iterate through all data.

    Each result can then be copied to the other sheet...


    Brian, ProcessIT- Hawke's Bay, New Zealand

    Monday, November 11, 2013 8:50 PM
  • Maybe something like this.

    Sub CopyData10()
    Dim Rng As Range, cell As Range
    Dim rw As Long
    Set Rng = Worksheets("Sick").Range("H1:H10")
    rw = 1
    For Each cell In Rng
    If LCase(cell.Value) = "x" Then
    Worksheets("Resumed").Cells(rw, "A") = cell.Offset(0, -1)
    rw = rw + 1
    End If
    Next
    End Sub


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Sunday, November 17, 2013 9:27 PM