Shaolin1976
05-09-2006, 03:09 AM
Hi all,
Johnske wrote some code to search Column A in all sheets of my workbook and copy each row which matched the desired string to another sheet as described on:
http://vbaexpress.com/forum/showthread.php?t=8008
And many, many thanks to Johnske for that :)
However, I now realise that it would be helpful if, as well as having the ability to search globally. I could just search the previous sheet (sheets are named 1 - 100) for the desired string (6 digit number found in column A, A6:A200) and populate the corresponding cells in it's row with the data found in that row on the previous sheet. I would like to have this work in the same way as VLOOKUP but without the huge computational effort and time loss that is associated with numerous (thousands) of VLOOKUP's.
The reason being that the data found on the previous sheet pertains to the previous month and this data will run on to the current and subsequent month(s); this data will not change from month to month.
So, to recap, is it possible for each worksheet (apart from the "SEARCH" sheet) to search only the previous worksheet for instances of a 6 digit number (in column A) and pull out the values in the corresponding cells in that row (B through Y) onto the next worksheet and have it do all of this automatically, i.e. without clicking a button or on Mouse Over and all of this without the CPU usage which comes with numerous VLOOKUPs
Nice and easy huh? hence my question. It's no problem if this is too complicated since I can make do with my VLOOKUPs but just thought I would ask the question anyway.
Many thanks
Shaolin
Johnske wrote some code to search Column A in all sheets of my workbook and copy each row which matched the desired string to another sheet as described on:
http://vbaexpress.com/forum/showthread.php?t=8008
And many, many thanks to Johnske for that :)
However, I now realise that it would be helpful if, as well as having the ability to search globally. I could just search the previous sheet (sheets are named 1 - 100) for the desired string (6 digit number found in column A, A6:A200) and populate the corresponding cells in it's row with the data found in that row on the previous sheet. I would like to have this work in the same way as VLOOKUP but without the huge computational effort and time loss that is associated with numerous (thousands) of VLOOKUP's.
The reason being that the data found on the previous sheet pertains to the previous month and this data will run on to the current and subsequent month(s); this data will not change from month to month.
So, to recap, is it possible for each worksheet (apart from the "SEARCH" sheet) to search only the previous worksheet for instances of a 6 digit number (in column A) and pull out the values in the corresponding cells in that row (B through Y) onto the next worksheet and have it do all of this automatically, i.e. without clicking a button or on Mouse Over and all of this without the CPU usage which comes with numerous VLOOKUPs
Nice and easy huh? hence my question. It's no problem if this is too complicated since I can make do with my VLOOKUPs but just thought I would ask the question anyway.
Many thanks
Shaolin