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
"Neil Holden" wrote:
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" wrote:
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" wrote:
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.