Concatenate and copy in VBA

[ANDYGM]

I am writing a macro to transfer data from one worksheet to another
Unfortunately, the data is in different columns which makes things mor
awkward and because not all data is to be included the macro includes
routine to find the lines of data to be transposed and then finds th
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 th
same row in the first sheet which have to be concatenated into one cel
in the second.

I am using a FOR ...NEXT loop to control the movement, so it is eas
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 ont
the other worksheet?

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

Thanks,

Andyg

 

[JE McGimpsey]

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.

 

[ANDYGM]

Excellent!

That works very well,

Thanks