ExcelBanter - Concatenating and Copying columns from 30 sheets

Hi,

i do apologise for any inconvinience i have caused you.. but that has worked
a treat.. really appreciate your prompt replies.

You mentioned duplicates - i dont expect there to be any duplicates because
one person can only appear in one of the 30 sheets once but incase the
unlikely happens - is there a problem that this can cause? i would assume if
will concatenate and put in the summary sheet anyways, right?

the duplicate will later be picked up as this list will be used for a
lookup. i didnt know how to do a lookup from the column E in each of the 30
sheets into 1 column in another sheet (is this even possible?) so that why i
thought i would get all the column E's from the 30 sheets, put them in 1 new
sheet that you have called summary and then my colleague would manually do a
vlookup from that column into another existing column. is there a way that
this bit can be automated?

i thank you sincerely for your help.

"Nigel" wrote:

This is a copy.......of the earlier trail, it includes the space, and
removal of blank rows on the summary sheet......
(watch out for word wraps in the code)

==================================================
It's always better to specify requirements more fully at the start, saves
rework, but here is the solution.

PS. Thought about duplicates in the list??

Sub CopyESum3()
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
.Columns("E:E").Insert shift:=xlToRight
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
With wSTotal
For aRow = sRow To 1 Step -1
If Len(Trim(.Cells(aRow, 1))) = 0 Then .Rows(aRow).EntireRow.Delete
shift:=xlUp
Next
End With
End Sub

--

Regards,
Nigel

"Zak" wrote in message
...
Sorry, i dont know if i am being blind but i cant seem to see the bit
about
inserting a space between C & D when concatenated, thats what i need to
know.
i am looking at all other responses to see where you say you have linked
the
response but i only see our old posts.

sorry to be such a pain!

"Nigel" wrote:

see above, linked to other thread trail

--

Regards,
Nigel

"Zak" wrote in message
...
Hi, i received an email that i had a response but i see no response
when i
look in forum.

please advise.

"Zak" wrote:

SORRY, i have got it to delete the blank cells i just need a space in
the
concatenate bit.

thanks so much.

"Nigel" wrote:

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.