Macro to Copy From One Workbook To Another

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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