wsh/vba for excel automation: moving worksheets beetween workbooks 
Author Message
 wsh/vba for excel automation: moving worksheets beetween workbooks

Hi,

I'm trying to use wsh/vba to automate actions required on a excel
workbook containing nearly 300 worksheets.

I need to break up the source workbook into multiple smaller workbooks
(averaging 2-5 worksheets each) based on the contents of the
individual worksheets.

I am able, programmatically, to open the source workbook, determine
from the contents of the each worksheet which destination workbooks
should contain it, create the destination workboko if nescessary, and
open the destination workbook.

I *cannot*, however, find a mechanism to transfer the entire worksheet
between workbooks :(

I understand that, worst case, I can probably do a data copy from the
existig worksheet and paste it into a new worksheet in the destination
workbook, but I want to avoid it if I can...

Any help greatly appreciated...



Wed, 22 Dec 2004 15:56:45 GMT  
 wsh/vba for excel automation: moving worksheets beetween workbooks
With help of the macro recorder, I got the following:

Sub Macro1()
    Sheets("Sheet1").Move After:=Workbooks("Book2").Sheets(1)
End Sub

Macro recorder - a great source for simple code, at times..

You may get in trouble if there are lots of (formula) links between the
sheets. In such case, I'd rather take one master copy of the file and then
delete the unnecessary sheets and save the file as a new copy. Then open
again the master file and go on with the deletion/saving procedure. Takes s
little more time, but if you're doing it programmatically, it should not be
a problem. Searching for hidden links is a pain in the a** (especially if
you have 50-150 workbooks to search for such links).

HTH,
Jouni


Quote:
> Hi,

> I'm trying to use wsh/vba to automate actions required on a excel
> workbook containing nearly 300 worksheets.

> I need to break up the source workbook into multiple smaller workbooks
> (averaging 2-5 worksheets each) based on the contents of the
> individual worksheets.

> I am able, programmatically, to open the source workbook, determine
> from the contents of the each worksheet which destination workbooks
> should contain it, create the destination workboko if nescessary, and
> open the destination workbook.

> I *cannot*, however, find a mechanism to transfer the entire worksheet
> between workbooks :(

> I understand that, worst case, I can probably do a data copy from the
> existig worksheet and paste it into a new worksheet in the destination
> workbook, but I want to avoid it if I can...

> Any help greatly appreciated...



Wed, 22 Dec 2004 16:49:59 GMT  
 wsh/vba for excel automation: moving worksheets beetween workbooks
Hi Shaun,

Have you tried something like this?

Workbooks("SourceBook").Sheets("Sheet1").Copy
Before:=Workbooks("DestBook").Sheets(1)

Charles Williams
______________________
Decision Models
The Excel Calculation Site
www.DecisionModels.com



Wed, 22 Dec 2004 16:54:57 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. wsh/vba for excel automation: moving worksheets beetween workbooks

2. How can I open Excel workbook without using Excel.Worksheet

3. VBA: Excel workbook to workbook transfer

4. deleting worksheet in excel workbook

5. Word/Excel Insert Single Worksheet from Workbook

6. function to see if an Excel worksheet name exitsts in a workbook

7. processing excel rows - separate workbooks/worksheets

8. Excel - Use the current workbook and worksheet

9. detect the worksheet-names in an EXCEL 5.0 workbook

10. Excel Workbook vs. Worksheet

11. Accessing a worksheet in an Excel workbook

12. Save a Worksheet in Excel to a Master Workbook

 

 
Powered by phpBB® Forum Software