I posted this prob yesterday but don't think I explained it very well.

I want to copy in excess of 50 different cell contents from one worksheet in
a workbook to a worksheet in a different workbook.

Rather than have reams and reams of recorded code that laboriously switches
between the two books I am hoping for any idea's to simplify the process and
make it easy to make any changes

The process has to be done on an individual cell by cell basis.

Any suggestions appreciated

How about:

Open both workbooks. Do the Copy, but Paste Special as Link. Then close
them. The next day, after you have changed the source workbook, leave it
open. Then open the workbook with the links (so both workbooks will be open)

Excel will probably ask you if you want to refresh the links and you should
agree.

No more cut/paste
--
Gary's Student


"Gazza" <mallin"nospam" wrote:

I posted this prob yesterday but don't think I explained it very well.

I want to copy in excess of 50 different cell contents from one worksheet in
a workbook to a worksheet in a different workbook.

Rather than have reams and reams of recorded code that laboriously switches
between the two books I am hoping for any idea's to simplify the process and
make it easy to make any changes

The process has to be done on an individual cell by cell basis.

Any suggestions appreciated

That suggestion won't work because I need to distribute the second workbook
so I don't want the links - also it's a rolling series of results over a 13
month period - the earlist month gets deleted and I need to import (or
manually type) the latest set of results so again I don't want any form of
linking.

Cheers for replying though.


"Gary's Student" wrote in message
...
How about:

Open both workbooks. Do the Copy, but Paste Special as Link. Then close
them. The next day, after you have changed the source workbook, leave it
open. Then open the workbook with the links (so both workbooks will be
open)

Excel will probably ask you if you want to refresh the links and you
should
agree.

No more cut/paste
--
Gary's Student


"Gazza" <mallin"nospam" wrote:

I posted this prob yesterday but don't think I explained it very well.

I want to copy in excess of 50 different cell contents from one worksheet
in
a workbook to a worksheet in a different workbook.

Rather than have reams and reams of recorded code that laboriously
switches
between the two books I am hoping for any idea's to simplify the process
and
make it easy to make any changes

The process has to be done on an individual cell by cell basis.

Any suggestions appreciated

I don't see the big problem. It is just as easy to switch from one
workbook to another as it is to switch from one worksheet to another.
Sounds like a simple for...next loop.

Sounds like something you want to be able to run again later after you
have updated some of the data.

John

To copy data from one worksheet/workbook to another by VBA code there is no
need to switch between them or select anything to begin with. So Gazza why
don't you just describe the structure of your data and the task in a bit
more detail? Maybe you could give the cell addresses and books/sheets names
and we try and put together a procedure for you. I guess you are
underestimating the VBA authomation big deal :-)

Regards,
KL


wrote in message
oups.com...
I don't see the big problem. It is just as easy to switch from one
workbook to another as it is to switch from one worksheet to another.
Sounds like a simple for...next loop.

Sounds like something you want to be able to run again later after you
have updated some of the data.

John

Agreed, no need to switch.
So long as we know to copy from where, to where, we can adopt this
Range("A1").Copy Workbooks("tmp.xls").Sheets(1).Range("B2")


--
Regards
PY & Associates
Excel customization service provider

"KL" wrote in message
...
To copy data from one worksheet/workbook to another by VBA code there is
no
need to switch between them or select anything to begin with. So Gazza why
don't you just describe the structure of your data and the task in a bit
more detail? Maybe you could give the cell addresses and books/sheets
names
and we try and put together a procedure for you. I guess you are
underestimating the VBA authomation big deal :-)

Regards,
KL


wrote in message
oups.com...
I don't see the big problem. It is just as easy to switch from one
workbook to another as it is to switch from one worksheet to another.
Sounds like a simple for...next loop.

Sounds like something you want to be able to run again later after you
have updated some of the data.

John

KL,

The simplified form of what I want to do is copy from :

Sheet CA in Book 1 Cells a1 to e1 into
Sheet CA_A in Book 2 Cells c2, c3, d4, f2, g4

(So content of cell a1 in Book 1 goes into cell c2 in book 2 and so on)


All I'm looking for is a start in doing this. The "real"spreadsheets are
much bigger than the example above

"KL" wrote in message
...
To copy data from one worksheet/workbook to another by VBA code there is
no need to switch between them or select anything to begin with. So Gazza
why don't you just describe the structure of your data and the task in a
bit more detail? Maybe you could give the cell addresses and books/sheets
names and we try and put together a procedure for you. I guess you are
underestimating the VBA authomation big deal :-)

Regards,
KL


wrote in message
oups.com...
I don't see the big problem. It is just as easy to switch from one
workbook to another as it is to switch from one worksheet to another.
Sounds like a simple for...next loop.

Sounds like something you want to be able to run again later after you
have updated some of the data.

John