Search Multiple Sheets and copy results

**discodave22** · 01-23-2014, 06:12 PM

Hi all

I am looking for some suggestions to help me in solving this issue. I have a workbook that has numerous worksheets of which 2 of the sheets are named "A" & "B". In each of these sheets. There are 5 columns in each of these 2 sheets and in column A there are dates in order starting from 01/01/14 continuous down the worksheet. Is there some way using either a userform or inputbox to prompt the user to enter a date and when the date is entered it will look in sheet "A" first and when it finds the date that was entered copy that row and the next 6 rows down ( to make a week ) into another worksheet " BOOKING INFO " once it has done this run the same code through sheet "B" using the same date and copying them rows to " BOOKING INFO". I have attached a workbook if it is of any help

Hope someone out there has got patience with me

Thanks Discodave22

**jmac1947** · 01-23-2014, 11:52 PM

Hi discodave22,

is the output from sheet B meant to go under the output from sheet A or should it go in cols (say) H - L and if in cols h+ do you need the date in two cols?

jmac

**jmac1947** · 01-24-2014, 12:26 AM

Hi discodave22,

take a look at the macro in the attached worksheet.

I think it does what you asked for so please test it. I haven't added in any error checking (like if the date you enter isn't found) so this is probably not a "production ready" solution but it should get you moving in the right direction

jmac

discodave22.xlsm

**discodave22** · 01-24-2014, 07:19 AM

hi jmac1947

Thanks for the quick response!

Yes that is exactly what i am looking for it works like a dream. If you could set the outputs from sheet B to go in the columns next to sheet A's output like you suggested then that will be this thread solved. Thank you again form your time

discodave22

**jmac1947** · 01-24-2014, 07:24 AM

Hi discodave22,

it is late friday night here in OZ so I will take a look at the change tomorrow sometime, should be within 24 hours

**jmac1947** · 01-24-2014, 07:18 PM

discodave22.xlsm

good morning discodave22,

take a look at the attached. I added in the heading rows in the output, took an easy way to display the output as requested and inserted a check that the date entered is actually in sheet A.

the macro does rely on the fact that there are only 5 columns in each input sheet, to extend that would simply require you to edit some range info in the macro

Jmac

**discodave22** · 01-25-2014, 05:30 PM

Hi jmac1947

Once again thank you that is spot on and exactly what I am after. I like the date check that is a nice touch !

How hard would it be for me to add more sheets in the future ie: C D E F etc... for this code to search through and output to the booking info sheet ? And again thank you very much this is very much appreciated

miff3436

**jmac1947** · 01-26-2014, 04:40 AM

Hi discodave22,

Happy to help, it also extends my knowledge as I see different problems and they need different solutions. I was an old fashioned "greenscreen" developer years ago so my code may seem a bit laboured at times as I am slowly learning the visual stuff that others take for granted

Now.. I was very tempted to say "yes it is easy, just change a few range values and repeat a bit of code and there you go" but then I thought, it shouldn't need code changes. SO I spent a couple of hours and generalised it so that hopefully it would be "a doddle"

I took my favourite approach and added a "Constants" worksheet to your workbook. This is probably the only spot you need to change if you add a new sheet.

Be warned however, the macro (as the comments at the beginning say) does expect the data tabs to ALL be before the BOOKING INFO and Constants tabs so when you add a new sheet, you will need to move it to before those two sheets manually.

I also added in functionality to allow more than 4 names per tab but I haven't tested that aspect.

Thanks for the opportunity to extend my VBA knowledge

If you think this is a solution then please use the thread tools to mark the thread solved

discodave22-Version 3.xlsm

Search Multiple Sheets and copy results

LinkBack

Thread Tools

Rate This Thread

Display

Search Multiple Sheets and copy results

Re: Search Multiple Sheets and copy results

Re: Search Multiple Sheets and copy results

Re: Search Multiple Sheets and copy results

Re: Search Multiple Sheets and copy results

Re: Search Multiple Sheets and copy results

Re: Search Multiple Sheets and copy results

Re: Search Multiple Sheets and copy results

Thread Information

Users Browsing this Thread

Similar Threads

Search multiple sheets and post results on listbox on form

Search multiple sheets and return list of multiple results

Filter Multiple Sheets and copy results to another

[SOLVED] Search multiple sheets, then paste results in new sheet

[SOLVED] Search multiple sheets, then paste results in new sheet

Bookmarks

Bookmarks

Posting Permissions