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.