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.

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.

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.

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.

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.

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
...
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.

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.

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.

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.

I suppose that works better - so it just concatenates in the summary sheet
-GREAT THANKS!. can i insert a space between the C&D concatenation? the last
statement in the macro should be to delete all blanks in column A. i have
this formula but im unsure of how to combine it with your one..it keeps
reporting error.

On Error Resume Next
Columns("A:A").SpecialCells(xlCellTypeBlanks).Enti reRow.Delete
ActiveSheet.UsedRange


THANKS ALOT.

"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.