Excel macro to pull retrieve data from multiple workbooks in a folder

Hi ppl,

i have been trying to find a macro to pull retrieve data from multiple work books from a same folder.

The criteria is i need to search for a particular string on each workbook sheet(a particular one), the search string could be any where on the work sheet and also some of the cells are merged, if it matches it has to copy the whole row on to a master sheet where it has the first column with file name on it.
Iam new to VBA Scritping and it is kinda complicated as we have to search a string which can be anywhere on the sheet(row or column not fixed) & also with some of the cells being merged & also there the string searching is repeated.

Thanks in Advance.

Hi Xladept,

Thanks for the quick response. i tried to execute the above code sent , iam getting an error which says "The information cannot be pasted because the copy area and the paste area are not the same size and shape. try one of the following 1.click a single cell and then paste 2.select a rectangle that's the same size and shape , and then paste".
Appreciate your help with it.
Thanks

Actually i did try with

Please Login or Register  to view this content.

it was giving the error message saying "The information cannot be pasted because the copy area and the paste area are not the same size and shape. try one of the following 1.click a single cell and then paste 2.select a rectangle that's the same size and shape , and then paste"

i changed it to

Please Login or Register  to view this content.

just to check if it was working and it did work, so can u please look into it... i need the filename as well.....

Appreciate your input.

Wow , thank you xladept we are getting close to what is needed.....
Iam only getting the rows once though i have multiple rows with the same search criteria from a workbook(i do have repeated strings in each workbook and have to copy all of them to the master sheet),and also is it possible to search for multiple search strings....
Your are great help and infact you are adept.....

iam getting the following error buddy...

Unable to get the FindNext property of Range class

Still no luck xladept.... getting the same error message....
Unable to get the FindNext property of Range class

wow man you are goooooooood!!!!!!!!!!
with regard to multiple search is it possible to search for multiple items instead of just one string(lets say my search strings are "Alpha","Delta","Zeta").
If it makes it easier(VBA Script) i can add a new sheet with the search strings in Column A(Multiple).

well i can sort it and then search for that string, all matters is filtering the rows which meet the criteria and paste it to a master sheet.
Thank you for being magnanimous....

it says Cant assign it to an array buddy.

its still the same. the message says "compile error: Cant assign to array"

Wow it is working now, is it possible to get the data in the same sequence/order as in the Workbook....

i have an idea to get the output as needed and that is can we print the row number of the copied one into 1 column and then sort it based on it(we get the same sequence/order as it is found in the workbook).

they go till M column.

Originally Posted by xladept

You know what? That's a great idea (and one that I will exploit in the future)!

Which column - how far do your columns go?

Please Login or Register  to view this content.

You can change Columns.Count to N (14)

Hey buddy, we are almost getting there iam getting an error which says "Method 'Range' of object'_worksheet' failed"
and it is on the sort part of the code. can you please look into it. Thanks.

Please ignore my earlier message, it is working now. let me look into the master list see if this what is needed!!!

Thank you very much XlAdept you been a big big help buddy.

Successfull!!!!!!!

Hello, well I tried to follow the thread but as it grew more specific for you guys, it grew to be out of my comprehension. I am attempting to do a similar task.

I have two, multi-sheet workbooks with thousands of part numbers (14 digit alph-numeric) and various descriptions. I need to combine and in some cases compare them.

So my first thought was to gather the info into a third, new workbook.
A macro to go through each workbook (yes they can all be in the same folder.) and search for any cell, on any worksheet, that contains the part numbers (14 digit alph-numeric) starting with "779"

If a cell contains a part number (14 digit alph-numeric) starting with "779" grab it's contents and the contents of the cell to it's right. Then paste that information into the third, new workbook.

It would be nice if all the part numbers from a workbook would be placed on one worksheet, and the other workbook's part numbers on the next worksheet.

Any idea how to do this? It would save me days of work.