search on multiple worksheets all in the same workbook

I have a workbook with 8 worksheets.
Each worksheet has similar data, but not exact.
Each Worksheet has 2 columns with the same title 'Description/Title' and 'Author'
I would like to have a macro that would search the two columns in common in all 8 worksheets.
I would like to have the macro include a box asking for the search string.
I would like the results of the search to include the worksheet name, 'description/title', 'Author', and the cell reference.
I would like the results to appear in a separate window where there is a button that allows me to print the results of the search.
I have seen several macros that do some of this, but not all.

I am curious how to get this to work

Is this the same as your previous thread minus the multiple workbooks requirement?

If so then this could be considered as a duplicate thread.

While this may seem similar to a previous thread, I believe that it is new as it deals with only one workbook, instead of multiple workbooks and folders
If this is deemed to be a duplicate, I apologize.

Hello Bighkyfan,

If I have understood your post correctly, then the following code may work for you:-

Please Login or Register  to view this content.

For whatever reason, this site's file uploader doesn't work on my end so following is a link to my test work book for you to peruse. There are only five individual sheets in the test work book plus the "Result" sheet. You should get the idea of how it works.

https://www.dropbox.com/s/08pbo66un8...refs.xlsm?dl=0

Once you enter an Author's name in the SEARCH box in cell A1 of the "Result" sheet (Sheet6) and click on the GO button, the code will filter each individual sheet for that name and summarise the details for you in the"Result" sheet. Click on the print button to print the results of the search.

You'll also see the source sheet and cell references in the "Result" sheet.

I hope that this helps.

Cheerio,
vcoolio.

In running this, nothing happens. It runs for a while then stops with nothing happening.
In looking at your data from your dropbox, this is almost exactly what I am looking for. One exception: I want this to search both the Author field AND the Title/Description field.
Other than that, this works just as I hoped. Now if I can just get it to work on my file. My file has 8 tabs, not six like yours.

Thanks for helping here.
When I say nothing happens, that's what I mean. Nothing is happening

I copied your code
I went into my spreadsheet and hit ALT F11 to open VBE.
I clicked on the modules icon to insert a new module.
I pasted your code into that window.
I hit F5 to run. I get an hourglass for 3-4 seconds then nothing happens.
All I see is the window with the coding in it and a flashing cursor

I checked the spreadsheet and there is no new tab with called Result, and there is no place to enter search and click GO

I clicked on your test workbook, and it still works fine. I like the second GO button

Let me know if I am doing something wrong here

Hello Bighkyfan,

You obviously haven't created a summary sheet named "Result" or created a search box and a button to assign the code to.

You don't actually need a search box as we could use an Input Box which will do the same thing. If you wish to experiment, then, in the code above, change the following line :-

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Enter the search criteria into the Input Box (it is case sensitive) and click OK or press enter to see the result.

You will of course need to create a summary sheet named "Result" (or you can just name it "Summary"). But remember to change the sheet references in the code to suit. In my test work book , I have referenced the "Result" sheet with the actual sheet code (Sheet6) except for this line

Please Login or Register  to view this content.

If you are still not sure, then upload a sample of your actual work book so that I can try and sort out whatever the issue is. Remember to be careful with any sensitive data.

Cheerio,
vcoolio.

Thank you very much. This is almost exactly what I am looking for.
However, sometimes the search will not give results when I know there should be.
Other times I will run it, and I will get results. But when I try to run it with the same search again, it will not have any results.
Other times it will give some results where the search term is not in either of the two columns
Also, I have an instance where I run the search for "Jefferson Barracks" and I get a list of results. But if I run the query on just "Jefferson", I don't get any results.

I hope you can help with this, I am so close!

In addition, when the results display, can you add coding that will expand the width of each column to be the same width as the data in the cell? (This is the same as double clicking on the line between two columns)

If you want, I can send you a copy of my file. There is nothing of any value in it (except to me)

Hello Bighkyfan,

Are you referring to Jason or me?

Regardless, upload a sample of your work book. We'll have a look.

Cheerio,
vcoolio.

Hi Jason,

I don't know any more! I'm lost! Not sure what the OP is after. You've probably seen my test work book which works as it should.

We just need the OPs actual work book to test.

Back to Bighkyfan.......................

Cheerio,
vcoolio.

Hi vcoolio,

I didn't look at your test workbook, just had a look at the code you provided.

A few things that stand out, your method of using a formula to insert the cell address into sheet will not work correctly with duplicates, if you consider that there could be multiple titles by an author, or the same title by multiple authors, your formula will give the range of the first match against all of the identical matches. Also returns the address of column B if the match is found in column A.

Your search method only allows for a match of the entire cell contents, not a partial match. (my original code was the same, the new version uses Instr for partial matches).

No doubt there will be some other things that we both have missed.

Hi Jason and Vcoolio
Thank you so much for your help.

The current version of the coding includes the changes that I asked for with regards to width of the columns in the Results Sheet.
I also am not seeing different results when running the query again.
That's the good news.

Now the bad news.

Now, when I run it, the results appear twice in the Results Sheet
In addition, after running the query, in the first tab of my spreadsheet, the second and third columns are blank. They have been cleared out.

It you can tell me how to do so, I would love to upload a demo copy of my file for you to examine.

Again, Thanks for your help.

Bob

I uploaded the file to my Dropbox account, but I don't know how to share the link here. Help!

Hi Bob,

You can upload a copy of your workbook by clicking the 'Go Advanced' button at the bottom left of the reply box, then scrolling down and looking for 'Manage Attachments'

If you were running my latest code then it should have added a new sheet as the first sheet, so it shouldn't be making changes to your data that was on the first sheet, that should now be intact on the second sheet.

Vcoolio's code adds some temporary data to the sheet which is deleted after the code is finished, so that could well be removing original data.

Not sure what could be causing the results to be entered twice though, maybe it will become more obvious when we have a copy of your sheet to test with.

Just had a quick look at the code and found the cause of the 2 errors.

The blank columns appears to have been the result of a typo in my code, 1 line was referring to the wrong sheet, that is an easy fix.

The duplicate entries are going to take a bit more thought. The part of the code that runs through all open workbooks doesn't seem to be working correctly, I can see what it is doing wrong, I'll have a look at fixing it in the morning (requires way too much thinking for 3.A.M.).

As far as I can see some workbooks are being processed more than once (quick test just repeated results 5 times).I have 5 workbooks open (not including the blank one with the code) so think it could be relative to that.

I'll post the revised code once I have it working properly.

Jason
I am attaching a copy of my file (called "Dummy Test")
I did want to let you know that I made a couple of small changes to your code. They should be just cosmetic, but you may want to check to make sure

Line 2 I changed SUB test() to SUB SearchResults()

Line 10 header = Array("Description/Title", "Author", "Worksheet Name", "Cell Reference") I removed "Worksheet Name" i don't need that in my results
Line 27 I removed the line rrng.Offset(rrow, 2) = wb. Name I am assuming that this was for the "Worksheet Name", which I don't need in my results.
For the next two lines, I changed the numbers from 3 and 4 to 2 and 3

Hopefully that didn't cause any problems.

Following up on the problem with duplicated and / or missing results.

Microsoft made some changes to the way workbooks are opened, previously opening another workbook from the excel file menu would open it in the same instance, where as opening from the windows / desktop explorer would open it in a new instance.

Excel 2013 or newer should open them all in the same instance regardless of the method used, unless you force a new instance, which requires some unconventional intervention (vba, Alt + right click, run command, etc.).

This may be specific to certain operating systems as well, I still use the old methods of opening a new instance when I'm at work, (Citrix / windows server OS) think excel is 2013, but possibly 2010.

Depending on which versions you are using and how you open the workbooks could affect the operation of the code, it will only run of workbooks in the same instance of excel.

There are ways to access other instances of excel, but I think trying to figure the correct code to do it properly would seriously damage my sanity

Jason,
Thank you so much. So far this latest version has been working perfectly.
If it is not to much to ask, I have a couple of cosmetic changes that I would like to have

On the Results page, can the Results be sorted by Worksheet Name, then by Cell Reference?
Can the Print Preview be in Landscape instead of Portrait? And can there be gridlines around each cell on the print preview?
I would also like to have a cell with the total number of results that match the criteria.

Is this too much to ask.

BTW, I am using Excel 2013.

Bob

Not too much to ask at all, Bob.

That was a quick and easy edit, I've also added a 'shrink to fit' option to it so that all columns fit on one page. Rows will still run to multiple pages if there are a lot of results, when this happens, the headers are also set to print on each page.

I've added gridlines to the print preview, but is the way that I have done it what you wanted, or do you need borders added to the sheet?

Please Login or Register  to view this content.

Thanks Jason, and Vcoolio
This works perfectly.

Hello Bob,

After seeing your work book, it dawned on me why my initial code created strange results for you. I refined it to suit your work book and return double cell references beside the sheet references (well, I'm still refining it!). I also maintained the one work book structure.
However, as I can see, Jason has sorted it out for you and, seeing that his sanity is at stake, you should stay with his resolution.
You may have to buy him a cold one or two (or do the English prefer theirs warm?)!

Anyway, I'm glad that I participated in this thread and thanks for recognising that Bob. There's always something to learn.

Kudos to Jason as well.

Cheerio people.
vcoolio.

Originally Posted by vcoolio

seeing that his sanity is at stake

Most of my sanity is long gone, I just didn't want to lose what little I have left trying to figure out running the code across multiple instances.

Why not post your code anyway? You might do something slightly different (or completely different), which may prove to be a better alternative.

Hi Bob

I've had a look at the code following the message you sent to me last week:-

This has been working fine, but I have noticed that the results include the worksheet name and cell reference. My users then have to take that info, go back to the workbook, navigate to the correct worksheet, and then to the correct cell reference to get additional information.

Is there any way to update this that would allow the results page to show more information than the four columns?
I would like the results screen to show Floor, Room, Bookcase, Shelf, and Item number. Each of the individual worksheets have columns with these headings

I noticed that not all sheets have an 'Item #' column, so I've written the code to check if it is missing in order to prevent errors. The other columns are individually checked on each sheet in case of any variation in position or order, but if one is missing or has a typo in the header then it will still cause 'Runtime error 5'.

It appears to work ok, but I've only done a quick test. Let me know if you need any changes.

Please Login or Register  to view this content.