Search workbook with many sheets for a value then pull that value into a cell in a new she

[funnybunny303]

I need very non-technical instructions on how to search every sheet in the same workbook (29 tabs) for the value in my current sheet (B2) find it on one of the 29 tabs, then pull the value from that sheet (B11) and the data from the range of cell on the same row (C11:G11) into the cell on my new worksheet (sheet 7, cell G2).

I have tried 4 VBAs and they havent worked. I cant get one macro to run. I could be doing it all wrong, but with a very basic understanding of these functions, I am sure I just not replacing the correct data from the sample formulas.

Thanks in advance for your help!

[JLGWhiz]

You can use the 'Find' method to search your sheets and then return columns B:G of that row on that sheet like this.

Please Login or Register  to view this content.

This code follows, as best possible, the criteria outlined in the OP. I question Sheet7 being the new sheet when there are already 29 sheets, but it is possible. I also question the validity of copying cell B11 plus cells(C:G) of that row and pasting to G2 on the destination sheet. At any rate, the code does the following:
1.puts the crfiteria sheet with cell B2 data in a variable.
2.Uses a For Next loop to check each sheet in the workbook, except the criteria sheet and sheet 7.
3.Uses the Find method to search for the B2 criteria on each of the other sheets.
4.If the B2 criteria is found, it copies the range B:G on that row and pastes values only to sheet 7, at cell G2.
5,If the value was found, it will exit the for loop without checking any other sheets.

Comments:
If the criteria value in B2 is only located in a specific column of the other sheets, that should have been specified since the code will otherwise check each cell of all the data on each sheet and that extends to run time of the macro considerably. The code was tested on a mock up and ran without error.