Copying and pasting a worksheet to a different excel workbook

[Neil Holden]

HI all,

I have an excel workbook which contains around 60 worksheets.

I need to have a bat file or a vb script to automatically copy one
particular worksheet into another excel document. The other excel document
will always be stored in one place but i want it to automatically update on a
daily basis.

Any thoughts would be much appreciated.

 

[Joel]

The way to do this is to put a workbook open macro into workbook. It can be
a new workbook that opens the two other workbooks and does the copying.
something like this



Private Sub App_WorkbookOpen(ByVal Wb As Workbook)

Book1Name = "c:\tmp\book1.xls"
Book2Name = "c:\tmp\book1.xls"

Set bk1 = Workbooks.Open(Filename:=Book1.Name)
Set bk2 = Workbooks.Open(Filename:=Book2.Name)

With bk2
'copy sheet from bk1 as a new worksheet
'at the end of bk2
bk1.Sheets("sheet1").Copy _
after:=.Sheets(.Sheets.Count)
End With
bk1.Close savechanges:=False
bk2.Close savechanges:=True

End Sub

 

[Neil Holden]

He Joel,

I have tried what you said but i don't think its quite what i want.

When the user saves his worksheet I need a macro to automatically pull
through data from one sheet through to another.

For example:

When Thom saves his workbook, macro to start and pull through certain data
from the summary sheet.

Difference Location - summary sheet which automatically updates from Neil's
sheet.

Every time i have to go into the summary sheet it needs to be up to date.

Regards.

 

[Joel]

I copied the worksheetd to the end. You can always copy the data from one
sheet to the same destination sheet. I change two lines in the code below.

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)

Book1Name = "c:\tmp\book1.xls"
Book2Name = "c:\tmp\book2.xls"

Set bk1 = Workbooks.Open(Filename:=Book1.Name)
Set bk2 = Workbooks.Open(Filename:=Book2.Name)

With bk2
'copy sheet from bk1 as a new worksheet
'at the end of bk2
'bk1.Sheets("sheet1").Copy _
' after:=.Sheets(.Sheets.Count)
bk1.Sheets("sheet1").Copy.cells _
Destination:=.Sheets("Thom Sheet").cells
End With

bk1.Close savechanges:=False
bk2.Close savechanges:=True

End Sub