Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to Copy From One Workbook To Another
I have two workbooks. I want to create a macro that copies information from
one workbook, which will be closed, to another workbook, which will be open. I would also prefer if this macro could run everytime the second workbook is opened. Is this possible? I would appreciate any help, Adam Bush |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to Copy From One Workbook To Another
Hi,
Yes it is possible, you just need to put it in the open event of the second book. You could either have the user choose the file (from a file picker) or have it hard coded in if you are always going to use the same file. As for coding you could use something along the lines dim wks as worksheet dim i as integer i=1 for each wks in "insert workbook name".worksheets wks.cells.copy 'insert original workbook name'.sheets(i).range("A1").pastespecial xlpastevalues i=i+1 next wks basically that will copy each worksheets cells into sheets (i) of your new book. I just wrote this off the top of my head to give you an idea of what to do. It's possible there are some compiling mistakes, since I don't work between workbooks on a daily basis. If you need more help I could give you a better defined macro which should do the trick. cheers, Scott " wrote: I have two workbooks. I want to create a macro that copies information from one workbook, which will be closed, to another workbook, which will be open. I would also prefer if this macro could run everytime the second workbook is opened. Is this possible? I would appreciate any help, Adam Bush |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to Copy From One Workbook To Another
Scott,
Thanks for the reply. The thing is I only want to copy over certain cells from one workbook to the other, not entire worksheets. Is this possible? Thanks Adam Bush "Scott" wrote: Hi, Yes it is possible, you just need to put it in the open event of the second book. You could either have the user choose the file (from a file picker) or have it hard coded in if you are always going to use the same file. As for coding you could use something along the lines dim wks as worksheet dim i as integer i=1 for each wks in "insert workbook name".worksheets wks.cells.copy 'insert original workbook name'.sheets(i).range("A1").pastespecial xlpastevalues i=i+1 next wks basically that will copy each worksheets cells into sheets (i) of your new book. I just wrote this off the top of my head to give you an idea of what to do. It's possible there are some compiling mistakes, since I don't work between workbooks on a daily basis. If you need more help I could give you a better defined macro which should do the trick. cheers, Scott " wrote: I have two workbooks. I want to create a macro that copies information from one workbook, which will be closed, to another workbook, which will be open. I would also prefer if this macro could run everytime the second workbook is opened. Is this possible? I would appreciate any help, Adam Bush |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to Copy From One Workbook To Another
Sure, use the same approach but instead of using
wks.cells.copy you could use things like workbook("abc").sheets("123").range("A1").copy then you could paste them where ever you want in the other book. If you want the easiest way (the least amount of code to write) start the macro recorder just before you do what you want done. Then when you finish stop it. Essentially you've just 'written' the macro that you want. Mind you the macro you get is usually pretty redundant and filled with useless steps, but it'd do the job. If you wanted it to work faster / trim some of the code, record it and post what you get in this discussion (just elimate any information you don't want shown). Cheers, Scott " wrote: Scott, Thanks for the reply. The thing is I only want to copy over certain cells from one workbook to the other, not entire worksheets. Is this possible? Thanks Adam Bush "Scott" wrote: Hi, Yes it is possible, you just need to put it in the open event of the second book. You could either have the user choose the file (from a file picker) or have it hard coded in if you are always going to use the same file. As for coding you could use something along the lines dim wks as worksheet dim i as integer i=1 for each wks in "insert workbook name".worksheets wks.cells.copy 'insert original workbook name'.sheets(i).range("A1").pastespecial xlpastevalues i=i+1 next wks basically that will copy each worksheets cells into sheets (i) of your new book. I just wrote this off the top of my head to give you an idea of what to do. It's possible there are some compiling mistakes, since I don't work between workbooks on a daily basis. If you need more help I could give you a better defined macro which should do the trick. cheers, Scott " wrote: I have two workbooks. I want to create a macro that copies information from one workbook, which will be closed, to another workbook, which will be open. I would also prefer if this macro could run everytime the second workbook is opened. Is this possible? I would appreciate any help, Adam Bush |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Help. Copy data from 1 workbook to another on next available | Excel Discussion (Misc queries) | |||
How can I copy a macro into another workbook? | New Users to Excel | |||
Copy macro to another workbook | Excel Discussion (Misc queries) | |||
Updating copy of Macro workbook... | Excel Discussion (Misc queries) | |||
copy text from 1 workbook to another by macro | Excel Worksheet Functions |