one way:

Dim sConCat As String
Dim i As Long
With ActiveCell
For i = 0 to 3
sConCat = sConCat & .Offset(0, i)
Next i
End with
Workbooks("Book2.xls").Sheets("Sheet1").Range("J1" ).Value = sConCat

in general, you should try to address ranges directly rather than
through Select/Activate. Doing so makes your code smaller, faster, and
IMO, easier to maintain.

In article ,
ANDYGM wrote:

I am writing a macro to transfer data from one worksheet to another.
Unfortunately, the data is in different columns which makes things more
awkward and because not all data is to be included the macro includes a
routine to find the lines of data to be transposed and then finds the
next blank line on the other sheet into which it is to be transposed.

My particular problem is that there are 4 adjacent cells of data in the
same row in the first sheet which have to be concatenated into one cell
in the second.

I am using a FOR ...NEXT loop to control the movement, so it is easy
enough to find the correct row by the following method, e.g.

ActiveCell.Offset(rowOffset:=N, columnOffset:=0).Activate

How can I select all 4 cells and concatenate them, then paste them onto
the other worksheet?

I can't change the format of the two worksheets.