+ Reply to Thread
Results 1 to 4 of 4

Search for Multiple String Values on Multiple Sheets and Copy Rows to New Sheet

  1. #1
    Registered User
    Join Date
    03-14-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2007
    Posts
    2

    Search for Multiple String Values on Multiple Sheets and Copy Rows to New Sheet

    Hey All,
    First time poster but I've been following the forum for a couple months now. New to VBA so excuse my ignorance. Basically, I've been compiling code from multiple sources but ran into a road block with this new project. The workbooks I'm working in have multiple worksheets (20+), and I'm looking to make a macro that searches for multiple string values ("PQ", "DQ", and "Visited") on all worksheets, and then copies the found entries and their respective rows to a Summary Report. The code I've put together searches the multiple string values but only on the first worksheet. I'd appreciate any help or advice to see if this was at all possible!

    Please Login or Register  to view this content.
    Thanks!

  2. #2
    Valued Forum Contributor AlvaroSiza's Avatar
    Join Date
    09-19-2007
    Location
    Staffordshire
    MS-Off Ver
    2007
    Posts
    591

    Re: Search for Multiple String Values on Multiple Sheets and Copy Rows to New Sheet

    I think you just need to nest your While loop within a For Each to loop through the worksheets in the workbook.

    Amended to Post #3
    Last edited by AlvaroSiza; 03-14-2012 at 10:48 PM. Reason: Better explanation in Post 3
    Perhaps it was the Noid who should have avoided me...
    If you are satisfied with my solution click the small star icon on the left. Thanks
    1. Make a copy of your workbook and run the following code on your copy (just in case)
    2. With excel open, press ALT+F11 to open the Visual Basic Editor (VBE). From the "Insert" menu, select "Module".
    3. Paste the code from above into the empty white space. Close the VBE.
    4. From the developer tab, choose "Macros", select the Sub Name, and click "Run".

  3. #3
    Valued Forum Contributor AlvaroSiza's Avatar
    Join Date
    09-19-2007
    Location
    Staffordshire
    MS-Off Ver
    2007
    Posts
    591

    Re: Search for Multiple String Values on Multiple Sheets and Copy Rows to New Sheet

    I'm also a bit confused as to the reasoning behind the use of CStr throughout. By definition, it is used to convert a value to a string. However the worksheet.rows property uses values, not strings, so I can find no reason to convert your declared integers to strings as was originally posted. I made some modifications. Give it a go and report back.

    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    03-14-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Search for Multiple String Values on Multiple Sheets and Copy Rows to New Sheet

    Thanks for your response! I tried running the code you pasted but I get an "End If Without Block If" debug message with this line of code: Wend End If Next ws

    To give you a little background, I took from this code, and then modified it to search multiple values:
    http://www.techonthenet.com/excel/ma...for_string.php

    I can also provide you with a sample worksheet, but I'd have to scrub the data in it. I appreciate your help!

+ 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