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,

[Marijus]

aWBkIHN1cHBvc2UgeW91IHRvIHVzZSBEQVRBfElNUE9SVCBFWFRFUk5BTCBEQVRBfElNUE9SVCBE
QVRBDQphbmQgaXQgd291bGQgbG9vayBsaWtlIHRoaXMuDQoxLiBZb3UgY3JlYXRlIG1hc3RlciBk
b2N1bWVudCAoTVcueGxzKQ0KMi4gYW5kIHRoZW4gZnJvbSBtYXN0ZXIgZG9jdW1lbnQgeW91IHVz
ZSBjb21tYW5kIE1PVkUgT1IgQ09QWSBTSEVFVCBhbmQgbWFrZSBhIGNvcHkgb2Ygc2hlZXQgdG8g
bmV3IGRvY3VtZW50LiAod2IxLnhscykgDQozLiB0aGVuIHlvdSBoYXZlIHRvIGNvbm5lY3QgbWFz
dGVyIGRvY3VtZW50ICB0byBjaGlsZCBkb2N1bWVudCB3aXRoIA0KREFUQXxJTVBPUlQgRVhURVJO
QUwgREFUQXxJTVBPUlQgREFUQSANCjQuIHRoZW4gc2VuZCBhbmQgcmVjZWl2ZSB0aG9zZSB3b3Jr
Ym9va3Mgd2l0aCBmcmVzaCBkYXRhDQo1LiBwdXQgcmVjZWl2ZWQgZmlsZXMgZXhhY3RseSBpbiB0
aGUgc2FtZSBkaXJlY3Rvcnkgd2hlcmUgdGhleSB3ZXJlLg0KNi4gb3BlbiBtYXN0ZXIgZG9jdW1l
bnQgYW5kIHVwZGF0ZSBkYXRhLg0KDQoNCg0KTS5BLg0KImhhbGVtMiIgPGhhbGVtMi4yNTU1bmhf
MTE0MzE0ODg4My41MTA4QGV4Y2VsZm9ydW0tbm9zcGFtLmNvbT4gd3JvdGUgaW4gbWVzc2FnZSBu
ZXdzOmhhbGVtMi4yNTU1bmhfMTE0MzE0ODg4My41MTA4QGV4Y2VsZm9ydW0tbm9zcGFtLmNvbS4u
Lg0KPiANCj4gSGk6DQo+IA0KPiBJIGhhdmUgYSB3b3JrYm9vayB3aXRoIGFib3V0IDI1MCB3b3Jr
c2hlZXRzLCBsZXQncyBjYWxsIGl0IE1XLnhscy4gIEFsbA0KPiB3b3Jrc2hlZXRzIGhhdmUgYSBu
YW1lKGExLCBhMiwgZXRjKS4gIE9uY2UgYSB3ZWVrIEkgY29weSBncm91cHMgb2YNCj4gd29ya3No
ZWV0cyBhbmQgc2F2ZSB0aGVtIGFzIGRpZmZlcmVudCB3b3JrYm9va3MobGV0J3Mgc2F5IHdiMS54
bHMsDQo+IHdiMi54bHMpLiAgVGhvc2Ugd29ya2Jvb2tzIGdldCB1cGRhdGVkIGFuZCBlbWFpbGVk
IGJhY2sgdG8gbWUuICBJDQo+IHJlY2VpdmUgYWJvdXQgMTUgd29ya2Jvb2tzIHRocm91Z2ggZW1h
aWwuICANCj4gDQo+IEkgbmVlZCB0byBjb3B5IGRpZmZlcmVudCBjZWxsIHJhbmdlcyBmcm9tIHRo
ZSB1cGRhdGVzIHdvcmtib29rcycNCj4gd29ya3NoZWV0cyAobGV0J3Mgc2F5IGZyb20gd2IxLnhs
cykgdG8gdGhlIG9yaWdpbmFsIG9uZShNVy54bHMpLiAgU28gaWYNCj4gaW4gdGhlIGVtYWlsZWQg
d29ya2Jvb2sgSSBoYXZlIHdvcmtzaGVldHMgYTEsIGEyLCBhMywgSSBuZWVkIHRvIGNvcHkNCj4g
Y2VsbCByYW5nZXMgYjU6azUgYW5kIGE3OiBqMTAgdG8gdGhlIHNhbWUgd29ya3NoZWV0cyBpbiB0
aGUgb3JpZ2luYWwNCj4gd29ya2Jvb2soTVcueGxzKS4gDQo+IA0KPiBJdCBpcyBhIHZlcnkgdGVk
aW91cyBwcm9jZXNzIGFuZCBpdCBpcyBpZGVudGljYWwgZm9yIGV2ZXJ5IHNoZWV0Lg0KPiANCj4g
VGhhbmsgeW91IGluIGFkdmFuY2UgZm9yIGFueSBoZWxwLDplZWs6IDplZWs6DQo+IA0KPiANCj4g
LS0gDQo+IGhhbGVtMg0KPiAtLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0NCj4gaGFsZW0yJ3MgUHJvZmlsZTogaHR0
cDovL3d3dy5leGNlbGZvcnVtLmNvbS9tZW1iZXIucGhwP2FjdGlvbj1nZXRpbmZvJnVzZXJpZD05
OTMwDQo+IFZpZXcgdGhpcyB0aHJlYWQ6IGh0dHA6Ly93d3cuZXhjZWxmb3J1bS5jb20vc2hvd3Ro
cmVhZC5waHA/dGhyZWFkaWQ9NTI1ODY5DQo+

[halem2]

thanks for the help. it was clear as day!

[CaptainQuattro]

Here's one solution:

You need to do the following preparation one time only.

1) Create 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.xls]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.

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,