Create Pivot table with data from 2 or more different spread sheets?

[evenflow357]

For my job I get sent large spread sheets with about 35,000 raws of data. Each spread sheet is the same layout and column headings.

I have been putting the data into the same spread sheet and using a pivot table to extract the data I need (that is from the overall data and not just one spread sheet). But this spread sheet where I have been getting the data is getting to large. So ideally is there a way I can insert data from different spread sheets into a pivot table? I looked into this and can link data from different tabs or sheets. But not different spread sheets/work books.

I would say I have good to advanced excel skills. I don't know VBA, but can edit the code if given instructions.

Thanks

 

[Fazza]

welcome

there is explanation http://www.mrexcel.com/forum/excel-questions/315768 and other places - please google

you don't need VBA; the old thread [note Excel 2003] initially answers a specific question asking for a VBA solution; later in the thread is discussion about solutions without code

cheers

 

[evenflow357]

Thanks for the response - but the link doesn't work.

I have searched into this issue - but not found any solutions as yet.

I found the link below but the macro is not working for me in excel 2010.

Pivot Table From Data in Multiple Workbooks

 

[Fazza]

Try again
http://www.mrexcel.com/forum/excel-questions/315768-creating-pivot-table-multiple-sheets.html

As I wrote already, that old thread (initially) addresses a particular request for a code solution. Further in the thread is other information. To change the code for later versions is minor, btw, and is likely just the connection string but I can' test that as I use Excel 2003 exclusively. But you don't need code to make the pivot table it is easy enough manually.

 

[evenflow357]

Thanks Fazza - sorry its been a while since I logged in. So a delayed thanks!