If you do not want to write it into column E, why not just write it into the
summary....

Sub CopyESum2()
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
wSTotal.Cells(sRow, 1) = Trim(.Cells(aRow, 3)) & Trim(.Cells(aRow, 4))
sRow = sRow + 1
Next
End With
End If
Next
End Sub


--

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.