+ Reply to Thread
Results 1 to 5 of 5

How to find a text string and then copy the next 5 rows to a new sheet

  1. #1
    Registered User
    Join Date
    04-19-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    1

    Question How to find a text string and then copy the next 5 rows to a new sheet

    Hi everyone, I have been searching and googling for days now and can't seem to find the right fit for what I need to do. I have tried piecing together different formulas that I could find, but I just can't seem to get it to work the way I want.

    What I am trying to do is make a macro that will search for the word "Voice" and then copy the next 5 rows to a new sheet. I have found plenty of macros that will do this, but I can't seem to figure out how to loop it to the end of the sheet and find each instance of the word "Voice".

    For example:

    Sheet1 would look something like this:

    | Column A |
    _____________
    1 Voice
    2 123
    3 456
    4 789
    5 011
    6 121
    7 314
    8 151
    9 617
    10 181
    11 Voice
    12 920
    13 212
    14 223
    15 242
    16 526
    17 272
    18 829


    So then in Sheet2 it would look like this:

    | Column A |
    _____________
    1 123
    2 456
    3 789
    4 011
    5 121
    6 920
    7 212
    8 223
    9 242
    10 526


    ... and this would repeat on until the last line in Sheet1.

    I appreciate any insight and help,
    Thank you!

    Jon

  2. #2
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: How to find a text string and then copy the next 5 rows to a new sheet

    Hi, Jon,

    maybe take the code from How to select, copy and paste rows from a find operation and modify ot a little bit to
    Please Login or Register  to view this content.
    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  3. #3
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: How to find a text string and then copy the next 5 rows to a new sheet

    Hey!

    Please find the file attached, and let me know if it is ok.

    Thank You,

    Deep
    Attached Files Attached Files
    Cheers!
    Deep Dave

  4. #4
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: How to find a text string and then copy the next 5 rows to a new sheet

    Hi, Deep,

    sorry but why donīt you paste the code here and attach a workbook? Itīs a Select code with switching sheets (unnecessary) and iterating thorugh all rows (while the counter could be augmented when the range is copied). Sadly your code only copies 4 row with
    Please Login or Register  to view this content.
    while the example and the opening post ask for
    ...that will search for the word "Voice" and then copy the next 5 rows to a new sheet.
    Ciao,
    Holger

  5. #5
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: How to find a text string and then copy the next 5 rows to a new sheet

    4 instead of 5 Rows was written by error.

    You can guide me with a better solution. I am always looking forward to learn from you..

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1