+ Reply to Thread
Results 1 to 10 of 10

Copy text based on criteria to a new worksheet (start-end)

  1. #1
    Forum Contributor
    Join Date
    02-05-2021
    Location
    Netherlands
    MS-Off Ver
    Microsoft Excel 365 (version 2022, Build 14931.20764)
    Posts
    180

    Smile Copy text based on criteria to a new worksheet (start-end)

    Dear Excel specialists,


    I have a small VBA programming question.

    I have multiple worksheets in one Excel-file. Worksheet: roadmap A, roadmap B, and so on in the future).

    I am looking for VBA code that:

    1. Searches through all the roadmap worksheets in this file (roadmap A, B, and to be added in the future).
    2. The script copies all rows (could be more or less/is variable) from (Start – roadmap) and (End - roadmap) and copies this data (rows) to the worksheet with the name: Desired_Result.
    3. As a result I would like to get a total overview of all worksheets that provide a roadmaps planning (including the row: Start roadmap and End roadmap).

    I found some code on the internet, but sometimes this code is too complicated for me, or it has a mismatch with the goal that a like to achieve (not exactly what I am looking for :-(.
    Can someone please help me out with this question?

    I attached the file to provide an example.

    Thank you very much (in advance for an answer)


    Best regards,



    Hans
    Attached Files Attached Files
    Last edited by NewBee_HS; 01-26-2022 at 06:26 AM. Reason: Forgot to upload the attachment.

  2. #2
    Forum Contributor
    Join Date
    02-05-2021
    Location
    Netherlands
    MS-Off Ver
    Microsoft Excel 365 (version 2022, Build 14931.20764)
    Posts
    180

    Re: Copy text based on criteria to a new worksheet (start-end)

    Not sure if I attached the (example) file correctly. This should do the trick.
    Attached Files Attached Files

  3. #3
    Valued Forum Contributor
    Join Date
    06-29-2014
    Location
    Australia
    MS-Off Ver
    MSO 365
    Posts
    1,095

    Re: Copy text based on criteria to a new worksheet (start-end)

    Hello Hans,

    See if this does the task for you:-

    Please Login or Register  to view this content.
    I hope that this helps.

    Cheerio,
    vcoolio.

  4. #4
    Forum Contributor
    Join Date
    02-05-2021
    Location
    Netherlands
    MS-Off Ver
    Microsoft Excel 365 (version 2022, Build 14931.20764)
    Posts
    180

    Re: Copy text based on criteria to a new worksheet (start-end)

    Hi Vcoolio,

    That is a super fast response and everything works like a charm.
    Thank you very much for you help. Highly appreciated! :-)

    Best regards,

    Hans

  5. #5
    Forum Contributor
    Join Date
    02-05-2021
    Location
    Netherlands
    MS-Off Ver
    Microsoft Excel 365 (version 2022, Build 14931.20764)
    Posts
    180

    Re: Copy text based on criteria to a new worksheet (start-end)

    Hi Vcoolio,

    My apologies... :-(
    I forgot to mention that there is also text below the "End - roadmap:" line/row. This was not shown in the example (sorry).

    So what happens now, is that all text mentioned below the "End - roadmap" is also added in the summary overview.
    My question is: Is it easy to stop and continue with adding the next worksheet when the text "End - roadmap" is found?
    Thank you in advance.

    Best regards,

    Hans

  6. #6
    Valued Forum Contributor
    Join Date
    06-29-2014
    Location
    Australia
    MS-Off Ver
    MSO 365
    Posts
    1,095

    Re: Copy text based on criteria to a new worksheet (start-end)

    Hello Hans,

    As your rows could increase or decrease in number, then, if you are willing to insert at least two blank rows directly below the "End - roadmap:" rows and leave them there permanently, we could do the copy/paste using the CurrentRegion method:-

    Please Login or Register  to view this content.
    Using the CurrentRegion method, the data set needs to be bounded by at least one blank row and one blank column hence any text below the data set will be excluded from the copy/paste. This can be included in the code if you'd prefer.

    I hope that this helps.

    Cheerio,
    vcoolio.

  7. #7
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Copy text based on criteria to a new worksheet (start-end)

    Quote Originally Posted by NewBee_HS View Post
    I am looking for VBA code that:

    2. The script copies all rows (could be more or less/is variable) from (Start – roadmap) and (End - roadmap) and copies this data (rows) to the worksheet with the name: Desired_Result.

    Can someone please help me out with this question?
    Quote Originally Posted by NewBee_HS View Post
    there is also text below the "End - roadmap:" line/row. This was not shown in the example.
    try
    Please Login or Register  to view this content.

  8. #8
    Valued Forum Contributor
    Join Date
    06-29-2014
    Location
    Australia
    MS-Off Ver
    MSO 365
    Posts
    1,095

    Re: Copy text based on criteria to a new worksheet (start-end)

    ....................and here's an option you may like even better:-

    Please Login or Register  to view this content.
    Cheerio,
    vcoolio.

  9. #9
    Forum Contributor
    Join Date
    02-05-2021
    Location
    Netherlands
    MS-Off Ver
    Microsoft Excel 365 (version 2022, Build 14931.20764)
    Posts
    180

    Re: Copy text based on criteria to a new worksheet (start-end)

    Hi Vcoolio,

    Many thanks! The last script works as a charm.
    Everything lined up as intented now, so again thank you very much for your time, effort and support!

    Best regards,

    Hans

  10. #10
    Valued Forum Contributor
    Join Date
    06-29-2014
    Location
    Australia
    MS-Off Ver
    MSO 365
    Posts
    1,095

    Re: Copy text based on criteria to a new worksheet (start-end)

    Hello Hans,

    You're welcome. I'm glad to have been able to assist and thanks for the reps.

    Please take note of Jindon's code. If you read through it, you'll notice that it is very efficient and doesn't beat around the bush!

    Cheerio,
    vcoolio.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 03-15-2017, 09:44 AM
  2. [SOLVED] Copy data from one worksheet to another worksheet based on criteria
    By kunal Shah in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 10-15-2015, 05:13 AM
  3. [SOLVED] Copy data from one worksheet to another worksheet based on criteria
    By kunal Shah in forum Excel General
    Replies: 2
    Last Post: 10-14-2015, 06:22 AM
  4. [SOLVED] VBA - Copy and Paste data based on two criteria to specific start row and column
    By ndgo10 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-02-2014, 09:15 AM
  5. [SOLVED] Copy rows from one worksheet to another worksheet based on a cell criteria.
    By keneco in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-07-2013, 10:28 AM
  6. Macro to copy worksheet data to another worksheet based on criteria
    By excelvb in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-07-2011, 04:11 AM
  7. Replies: 11
    Last Post: 01-04-2010, 11:40 PM

Tags for this Thread

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