Concatenate and copy in VBA
 

:confused:
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

--
Message posted from http://www.ExcelForum.com

Concatenate and copy in VBA
 

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.

Concatenate and copy in VBA
 

Excellent!

That works very well,

Thanks

--
Message posted from http://www.ExcelForum.com