import multiple excel books to one excel workbook

[Narendra Boga]

Dear All,

I have 95 excel workbooks each having 9000 to 18000 rows of data. I need to
prepare 1 consolidated excel workbook which shows all the data taken from 95
excel workbooks. I know the import option but it imports only 1 workbook
each time. Is there any option to import all the 95 worksheets to one single
workbook at a time? If the imported data is more than 65000 rows it should
import to next sheet. Any option?

Thanks in advance.

Narendar

 

[Joel]

You need to use a macro. I can write the macro but I was wondering if you
need to do any other manipulation of the data beside putting the data
sequentally into the workbook. Most cases people want some sort of summary
sheet or combining of the data.

 

[Narendra Boga]

I don't want to manupulate anything but I need to sort/filter out and compare
with my specified collection of data which is in master excel. The basic
need is, I need to compare and analyse the master data with the 95
worksheets. It need to check one specific item in master excel with all the
95 excels. I dont know that specific item is in which sheet? So, I need to
consolidate all the 95 excel sheet and going forward to my own analysis.
This is all I need to do.

 

[Joel]

Because of the large amount of data what people normally do is to search for
the specific item needed and only import the rows that match the item. this
is really very simple to do. To put the data into multiple sheets is
difficult and will make you job of analyzing the data harder than it need to
be. Can you describe what items you need?