MrExcel Publishing
Your One Stop for Excel Tips & Solutions

How to copy worksheets in dif physical locations to my workbook


Posted by Janet Wasteney on February 04, 2002 9:19 AM

I have 20 spreadsheets scattered across the state which
have information updated peroidically in each location.
With the exception of the name and the data in the cells,
these spreadsheets are exactly the same - formulas,
column placements, macros, etc. Each office will be
saving their own spreadsheet with a unique name to a
location on our network where I can access them.

Once a month I need to copy 250 rows in Columns A-L
from each spreadsheet and paste into a master workbook
on my computer, with each of the 20 spreadsheets being
in its own sheet in that workbook. I don't care if any
formulas are copied - I just need the data in the cells.

I've tried a variety of methods but none seem to work
right. Is there an "easy" way to do this?

Thanks!


Posted by Yogi Anand on February 04, 2002 11:21 AM

Posted by Yogi Anand on February 04, 2002 11:32 AM

Well Janet:
From what you have said, it appears you are trying to consolidate the data from various worksheets into a single sheet. You can use Excel's DATA|CONSOLIDATE feature to accomlish this.

Consolidated Data into Book3_Sheet1
from Book1_Sheet1 and Book2_Sheet1
3 3 3
3 3 3
3 3 3
3 3 3
3 3 3
3 3 3
3 3 3
3 3 3
------------------------------

Book1 Source data

1 1 1
1 1 1
1 1 1
1 1 1
1 1 1
1 1 1
1 1 1
1 1 1
-------------------------------

Book2 Source Data

2 2 2
2 2 2
2 2 2
2 2 2
2 2 2
2 2 2
2 2 2
2 2 2
-------------------------------

When youn choose DATA|COSOLIDATE, ahelpful dialog box would pop up, and will lead you step by step to identify your source data to consolidate. You will find it to be pretty straight forward.

HTH

Yogi Anand
ANAND Enterprises (broken link)

Posted by Janet Wasteney on February 05, 2002 8:46 AM

:Thanks - I'll give that a whirl!!

Well Janet: