Extracting data from one spreadsheet to another

**floricita** · 09-23-2010, 06:01 AM

Hi there.

I need to do the following.

I have several spreadsheets each with different reports. Each spreadsheet has 52 tabs denoting different areas. These are the same in each spreadsheet.

What I need to do is "take" out each tab from each spreadsheet, and collate all the similar tabs, i.e all area 1 together in one new spreadsheet then all area 2 tabs from each spreadsheet, put in new spreadsheet.

so common term is worksheet tabs are all having same names.

Other possible problems, is that in each spreadsheet because of the different amount of data sheets there are, area 1 may start on a different tab to the others.

the other thing is that each sheet needs to be inserted in an exact order in the new spreadsheet created.

any help with the principles behind this would be much appreciated.

**davesexcel** · 09-23-2010, 08:40 AM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

**floricita** · 09-23-2010, 09:24 AM

I'm not sure if I can download 20 work books on here though?

The basic principle is

that I have the following reports (currently)

Balanced Scorecard
Shop Performance Report

Top Potential Risk Customers
Top Turnover Customers
Top High Value Customers
Machines Performance Summary
No Notes & Coins
Late Openings
Machines Downtime
Tournaments
MDA
Sickness
Vending Concessions
Think 21 EPoS Entries
Security Audit
EPoS Errors
Board Price Errors

I don't think how the report is processed is important.

We have 52 areas. Each report produces a report for each area on a sperate tab (all tabs have same name on each report)

What I need to do is find a way to take each area tab out of each report by using the unique area name, and placing them in the above order in a new workbook.

currently all the reports above are dumped in one folder.

so in human language I need to go to the folder, go into balance scorecard, extract area 1 data, create new spreadsheet, paste tab 1, go back to folder, repeat area 2 etc...............return to folder, go to tab "area 1" in shop performance, copy all data, go to area 1 new workbook, paste after previous report etc etc etc.

I'm not sure how to write that in computer speak though?

Any help would be great

(all copying of data would be by using the crtl shift end down function, so basically all data-not all reports start on same page hence why need to use the reference of the tab rather than gooing straight to the first tab)