Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Search for text, cut and paste to another sheet (Excel xp)

    I recorded a macro to search for #page and then cut the row plus all the rows beneath it to row 200. This is the code i came up with. What do I do if the #page is not on Row 23? How can I grab ahold of the row where #page occurs plus all the rows through 200 and paste them to the new worksheet? thank you for your help


    Sub SplitTable()
    Range("A1").Select
    Cells.Find(What:="#page", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False).Activate
    Rows("24:200").Select
    Selection.Cut
    Sheets(1).Select
    Range("A1").Select
    ActiveSheet.Paste
    Range("A45").Select
    Sheets("Sheet1").Select

    End Sub


    In addition I have to write a macro to search for #page and then copy all the rows above the cell to row 1 to another worksheet. Thank you

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search for text, cut and paste to another sheet (Excel xp)

    Hi,

    Selecting is not needed in VBA and runs much faster.

    Sub SplitTable()
    Dim oFoundCell as range
    With activesheet
    set oFoundcell =.Cells.Find(What:="#page", After:=.range("A1"), LookIn:=xlFormulas, LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False)
    if ofoundcell is nothing then exit sub 'found nothing!
    .range(oFoundcell,.cells(200,1)).entirerow.Cut Destination:=Activeworkbook.worksheets("Sheet1").[a1]
    End With
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Search for text, cut and paste to another sheet (Excel xp)

    Thank you very much

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Search for text, cut and paste to another sheet (Excel xp)

    Also, what if i want to search for the words "Total Amount" and add a blank line above it? Can this code be modified for that? thanks

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search for text, cut and paste to another sheet (Excel xp)

    Sure:

    Sub FindAndInsert()
    Dim oFoundCell as range
    With activesheet
    set oFoundcell =.Cells.Find(What:="Total Amount", After:=.range("A1"), LookIn:=xlFormulas, LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False)
    if ofoundcell is nothing then exit sub 'found nothing!
    oFoundCell.Rows.Insert
    End With
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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