Copy Worksheets from one Workbook to Another

[halem2]

Hi:

I have a workbook with about 250 worksheets, let's call it MW.xls. All
worksheets have a name(a1, a2, etc). Once a week I copy groups of
worksheets and save them as different workbooks(let's say wb1.xls,
wb2.xls). Those workbooks get updated and emailed back to me. I
receive about 15 workbooks through email.

I need to copy different cell ranges from the updates workbooks'
worksheets (let's say from wb1.xls) to the original one(MW.xls). So if
in the emailed workbook I have worksheets a1, a2, a3, I need to copy
cell ranges b5:k5 and a7: j10 to the same worksheets in the original
workbook(MW.xls).

It is a very tedious process and it is identical for every sheet.

Thank you in advance for any help,

 

[halem2]

thanks for the help. it was clear as day!

 

[CaptainQuattro]

Here's one solution:

You need to the following preparation one time only.

1) Create a 2 duplicates of the MW workbook. As an example, call
them MW1 and MW2.

2) In the original workbook, In the ranges that you would normally
paste updated values into, create formulas that link to the
corresponding cells in MW1. So in your example, you would replace the
value on sheet A1, cell b5 with a formula that reads =[MW1]A1!B5

The remaining steps are repeated weekly:

3) Now you can split up workbook MW2 for emailing. Do so using the
following method:

Click on the tab for sheet first sheet in the group, e.g. A1.
Hold down the shift key and click on the Tab for the last sheet of the
group, e.g. A30
Right click and select Move or Copy.

In the "To Book" dropdown, choose (new book) and click OK. Save this
new book as WB1 or WB2, as required, and send it off by email.

Let's assume book WB1 contains tabs A1 through A30

4) When the recipient returns the completed WB1 file, do the following
steps WITHOUT OPENING the original MW file:

A) Open book MW1.xls
B) Delete sheets A1 through A30 in book MW1.xls
C) Select all of the sheets in book WB1 using the method I described
above and move them into workbook MW1.xls
D) Save workbook MW1.xls

Now it is safe to open book MW, and the values in sheets A1 through A30
will have magically been updated.

Hope this helps.