Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying and pasting a worksheet to a different excel workbook
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying and pasting a worksheet to a different excel workbook
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying and pasting a worksheet to a different excel workbook
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying and pasting a worksheet to a different excel workbook
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying and pasting links within the same workbook | Excel Worksheet Functions | |||
copying & Pasting Multiple Ranges within a workbook | Excel Programming | |||
VBA copying & pasting into a different workbook | Excel Programming | |||
Copying and Pasting a Worksheet | Setting up and Configuration of Excel | |||
Copying and pasting entire workbook | Excel Programming |