Hi, that has worked but i realised when it concatenated it didnt put a space
between the words - sorry i didnt specify that. It is first name and surname
that i am combining so would like a space can you please advise?

Also, columns until Y-Z have info in them and i realised when i run the
macro it replaces the current contents of the column E, so can you please put
in macro so it moves it only one column along - even if it was to insert a
new column after D (which would obviously be called E) and then to put the
result of the concatenation in that - so it doesnt replace any info.. that
would be very useful.

finally, can i put in a statement in the macro to delete all blank cells in
summary sheet column A, as the result brings back names concatenated but it
has lots of blank cells in it too.

i really appreciate your prompt reply. really helped me a lot.

"Nigel" wrote:

Should be two lines as follows......

For aRow = 1 To .Cells(.Rows.Count, "C").End(xlUp).Row
.Cells(aRow, 5) = Trim(.Cells(aRow, 3)) & Trim(.Cells(aRow, 4))

No it writes in column E, as per your OP. You will have to add a move data
process, what if there is stuff in column F, G, H etc. how far do you move
it?

--

Regards,
Nigel




"Zak" wrote in message
...
I tried running it and it reported an error and highlights this line:

For aRow = 1 To .Cells(.Rows.Count, "C").End(xlUp).Row.Cells(aRow, 5) =
_
Trim(.Cells(aRow, 3)) & Trim(.Cells(aRow, 4))

its not a problem if there is something already in column E right? i would
assume the macro would move along whatever is in the column and replace it
with the results of the concatentation.

please advise.

thanks.

"Nigel" wrote:

Sub CopyESum()
Dim wS As Worksheet, wSTotal As Worksheet
Dim lRow As Long, aRow As Long, sRow As Long

Set wSTotal = Worksheets.Add

wSTotal.Name = "Summary"
sRow = 1

For Each wS In Worksheets
If wS.Name < "Summary" Then
With wS
For aRow = 1 To .Cells(.Rows.Count, "C").End(xlUp).Row.Cells(aRow, 5)
= _
Trim(.Cells(aRow, 3)) & Trim(.Cells(aRow, 4))
Next
.Range(.Cells(1, 5), .Cells(aRow, 5)).Copy _
Destination:=wSTotal.Cells(sRow, 1)
sRow = sRow + aRow - 1
End With
End If
Next
End Sub


--

Regards,
Nigel




"Zak" wrote in message
...
Hi,

I have 30 sheets and for each of the 30 sheets firstly i would like to
concatenate columns C and D (in each sheet) then get the results of
that
(which would be displayed in column E) and then this column E to be
copied
and pasted in a new sheet - so all column E's in the 30 sheets to be
pasted
in 1 new sheet directly below one another. Is this do-able?

summary-
1-concatenate columns C & D in each of the 30 sheets
2-the results of this concatenation to be displayed in column E
3-this column E to be copied in each of the 30 sheets and pasted in a
new
sheet - each column directly under one another.

please reply at your earliest possible convinience.

thanks a lot.