Copying a range of values between worksheets

[Christopher R. Lee]

Hi,

We often have to copy data from one worksheet to another (usually between a
hidden unprotected sheet with a DDE link to other software, and a visible
protected one).

Before we updated to W2000/XL2000, the obvious kludge of putting
'=othersheet!C20' (etc) in each cell of the visible (destination) sheet
worked OK. There
are usually a few hundred cells involved.

Now, we get #REF! messages on random occasions, whether or not the DDE link
to the other software is activated.

Our workaround was to write some VBA to copy ranges of cells, with the
ranges and offsets defined by constants. Writing the code was surprisingly
difficult, and the result looks too messy to be worth posting.

We've searched the web and this ng for a neat solution, but may have missed
something.

Does anyone have a 'Range_Copy' macro that's both tidy and easily
understandable by
someone who wants to adapt it to a particular application?

Regards

 

[K Dales]

Copying a range is easy (and does not need to be done cell by cell, as long
as the layout in the destination is the same as the layout in the original) -
in fact, you could copy the whole worksheet - even with an offset:

Worksheets("Sheet1").UsedRange.Copy
Worksheets("Sheet2").Range("B2").PasteSpecial xlPasteValues
' This duplicates the layout of sheet1 on sheet2 moved over 1 cell and down 1

But it sounds like you need something more difficult - a particular mapping
of individual cells from one location on the first sheet to some other
location on the second. What makes it difficult to give a good response is
the particulars of your workbook, that is, what is the source range or ranges
and what are the destinations? If you have a mess of scattered cells on one
sheet that all need to go into different addresses on a different sheet -
again all scattered and not related in any logical way to the original layout
of the cells on the source sheet - there is no solution but to "hard code"
each source-destination. But if there is a logic, perhaps that logic could
be duplicated in code.

But any specific routine would require more information about the specifics
of the workbook: How are we to know which cells need to be copied? And how
are we to know the destination? Is there some logical way the destination is
related to the source?

 

[Christopher R. Lee]

Many thanks, didn't think of looking for copy/paste commands.

My original post wasn't too clear. The aim is to copy blocks of cells such
as B10:J59 to known destinations with the same dimensions, for example
A1:I50.

Regards