Search, copy and paste entire row into new workbook

I need a code that will search column A over multiple sheets for a user specified value through an input box. I then need this code to copy and paste the entire row to a new workbook titled "Batch Report" Anyone have any ideas? I have a code that will copy and paste only a few rows but no where near all of them, and I cant figure out why. Thanks.

This seems to be close to what I am looking for, except I get a runtime errror 1004 after. Also I want to pre-format the sheet that the batch numbers will be sent to so that it matches the sheets that they will be taken from. So how would I make these selections be placed into the next available row rather than row 1.

Thanks

I have attached my workbook and have the code under button 28. My goal is to create a new workbook that is formatted like the sheets on the original workbook so when I go to generate a batch report, the rows start to copy in the next available row. Also when I hit cancel on the inputbox I get a different runtime error, and Im not sure how to fix that. Any help would be greatly appreciated.

Thanks

Hello,

You have done an amazing job that is almost exactly what I was looking for. I am however, getting an unknown number in column I, which I believe should be the "date ordered," but i am getting a number in the thousands. Also, I love how you set the code up to transfer the colums headers. Would it be possible to add the column headers to colums I, J, and K as well. These can be seen on all sheets except the first one in my workbook. Also, is it possible to merge cells 3 and 4 like I have it in my main workbook. If you could help me out with these, that would be great.

Thanks

That is the exact code I have been looking for thank you very much. I just wanted to make sure that this code is set up to search the entire column A no matter how many pages I may have, because eventually I will have a few thousand rows. Also will it search every sheet, because I plan to have a couple hundred sheets as well? But thanks again you have configured the code exactly how I wanted. Thanks


Andrew

Originally Posted by D_Rennie

Yes the code will work on every sheet in the workbook.
Its not realy searching every row the code places a autofilter on the sheet and copies those rows to the destination book. then removes the autofilter and moves onto the next sheet.

Glad i could help

cheers

Hi there,
I have a excel workbook containing 20,000 rows of data. I will have to develop a macro to copy a certain range of data from that excel workbook & paste the same in another excel workbook, rename the workbook & save. Suppose one column contains certain rows with values as 1, next set of rows as column value=2, another set of rows as values =3. I will have to take all the rows corresponding to value 1 and paste in the second excel workbook, rename, save & close. This process repeats for column values=2 & 3 as well.Please help in writing the Macro Code. It seems preety similar with a slight different touch. your answer will be well appreciated.

The source Excel resides in the location:E:\Ani\SIMS
The destination excel resides in the location :C:\AM\SIT