I would like columns C & D to concatenate with a space and a comma seperating
the two words in each of my many sheets. the columns remain the same all the
time.

How do i do this? i tried using the concatenate formula in my macro code but
it didnt work as i dont really know how to refer to all sheets. Ideally i
would like a click of a button to concatenate C & D (with comma and space) in
each of my sheets.

thanks.

Maybe this,

Alt + F11 to open vb editor. Double click 'This Workbook' and paste this in

Sub renameall()
'Scroll through worksheets
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Select
Lastrow = Range("C65536").End(xlUp).Row
Set myrange = Range("C1:C" & Lastrow)
For Each c In myrange
c.Offset(0, 2).Value = c.Value & "," & c.Offset(0, 1).Value
Next

Next ws
End Sub


Mike

"Gemz" wrote:

> I would like columns C & D to concatenate with a space and a comma seperating
> the two words in each of my many sheets. the columns remain the same all the
> time.
>
> How do i do this? i tried using the concatenate formula in my macro code but
> it didnt work as i dont really know how to refer to all sheets. Ideally i
> would like a click of a button to concatenate C & D (with comma and space) in
> each of my sheets.
>
> thanks.

Missed you wanted a space so substitute this line

c.Offset(0, 2).Value = c.Value & " , " & c.Offset(0, 1).Value

Mike

"Gemz" wrote:

> I would like columns C & D to concatenate with a space and a comma seperating
> the two words in each of my many sheets. the columns remain the same all the
> time.
>
> How do i do this? i tried using the concatenate formula in my macro code but
> it didnt work as i dont really know how to refer to all sheets. Ideally i
> would like a click of a button to concatenate C & D (with comma and space) in
> each of my sheets.
>
> thanks.

I tried that but get a big X and '400' error!

i selected 'this workbook' from the left hand side somwhere and pasted the
code there.

please advise..

"Mike H" wrote:

> Missed you wanted a space so substitute this line
>
> c.Offset(0, 2).Value = c.Value & " , " & c.Offset(0, 1).Value
>
> Mike
>
> "Gemz" wrote:
>
> > I would like columns C & D to concatenate with a space and a comma seperating
> > the two words in each of my many sheets. the columns remain the same all the
> > time.
> >
> > How do i do this? i tried using the concatenate formula in my macro code but
> > it didnt work as i dont really know how to refer to all sheets. Ideally i
> > would like a click of a button to concatenate C & D (with comma and space) in
> > each of my sheets.
> >
> > thanks.

Sorry that was my fault, I should have said
right click 'This workbook'
Insert module and paste the code into the new module.

Mike

"Gemz" wrote:

> I tried that but get a big X and '400' error!
>
> i selected 'this workbook' from the left hand side somwhere and pasted the
> code there.
>
> please advise..
>
> "Mike H" wrote:
>
> > Missed you wanted a space so substitute this line
> >
> > c.Offset(0, 2).Value = c.Value & " , " & c.Offset(0, 1).Value
> >
> > Mike
> >
> > "Gemz" wrote:
> >
> > > I would like columns C & D to concatenate with a space and a comma seperating
> > > the two words in each of my many sheets. the columns remain the same all the
> > > time.
> > >
> > > How do i do this? i tried using the concatenate formula in my macro code but
> > > it didnt work as i dont really know how to refer to all sheets. Ideally i
> > > would like a click of a button to concatenate C & D (with comma and space) in
> > > each of my sheets.
> > >
> > > thanks.

I now get a run time error 1004 and it highlights "ws.Select".

i have the workbook open, is there something im doing wrong? does it matter
if each worksheet is named and not called sheet..?

sorry about this!

thanks for help

"Mike H" wrote:

> Sorry that was my fault, I should have said
> right click 'This workbook'
> Insert module and paste the code into the new module.
>
> Mike
>
> "Gemz" wrote:
>
> > I tried that but get a big X and '400' error!
> >
> > i selected 'this workbook' from the left hand side somwhere and pasted the
> > code there.
> >
> > please advise..
> >
> > "Mike H" wrote:
> >
> > > Missed you wanted a space so substitute this line
> > >
> > > c.Offset(0, 2).Value = c.Value & " , " & c.Offset(0, 1).Value
> > >
> > > Mike
> > >
> > > "Gemz" wrote:
> > >
> > > > I would like columns C & D to concatenate with a space and a comma seperating
> > > > the two words in each of my many sheets. the columns remain the same all the
> > > > time.
> > > >
> > > > How do i do this? i tried using the concatenate formula in my macro code but
> > > > it didnt work as i dont really know how to refer to all sheets. Ideally i
> > > > would like a click of a button to concatenate C & D (with comma and space) in
> > > > each of my sheets.
> > > >
> > > > thanks.

Gemz,

I can't replicate that error and no it doesn't matter what your sheets are
called. Looking at the code again it doesn't really matter if its in a
module or a worksheet it should still work. I am confused by your comment
that 'I have the workbook open'. You have pasted the code into the workbook
you want to concatenate haven't you

Mike

"Gemz" wrote:

> I now get a run time error 1004 and it highlights "ws.Select".
>
> i have the workbook open, is there something im doing wrong? does it matter
> if each worksheet is named and not called sheet..?
>
> sorry about this!
>
> thanks for help
>
> "Mike H" wrote:
>
> > Sorry that was my fault, I should have said
> > right click 'This workbook'
> > Insert module and paste the code into the new module.
> >
> > Mike
> >
> > "Gemz" wrote:
> >
> > > I tried that but get a big X and '400' error!
> > >
> > > i selected 'this workbook' from the left hand side somwhere and pasted the
> > > code there.
> > >
> > > please advise..
> > >
> > > "Mike H" wrote:
> > >
> > > > Missed you wanted a space so substitute this line
> > > >
> > > > c.Offset(0, 2).Value = c.Value & " , " & c.Offset(0, 1).Value
> > > >
> > > > Mike
> > > >
> > > > "Gemz" wrote:
> > > >
> > > > > I would like columns C & D to concatenate with a space and a comma seperating
> > > > > the two words in each of my many sheets. the columns remain the same all the
> > > > > time.
> > > > >
> > > > > How do i do this? i tried using the concatenate formula in my macro code but
> > > > > it didnt work as i dont really know how to refer to all sheets. Ideally i
> > > > > would like a click of a button to concatenate C & D (with comma and space) in
> > > > > each of my sheets.
> > > > >
> > > > > thanks.

thanks it works now but it takes a while to run.. there are like 20 sheets
but isnt there a way to speed up?

thanks.

"Mike H" wrote:

> Gemz,
>
> I can't replicate that error and no it doesn't matter what your sheets are
> called. Looking at the code again it doesn't really matter if its in a
> module or a worksheet it should still work. I am confused by your comment
> that 'I have the workbook open'. You have pasted the code into the workbook
> you want to concatenate haven't you
>
> Mike
>
> "Gemz" wrote:
>
> > I now get a run time error 1004 and it highlights "ws.Select".
> >
> > i have the workbook open, is there something im doing wrong? does it matter
> > if each worksheet is named and not called sheet..?
> >
> > sorry about this!
> >
> > thanks for help
> >
> > "Mike H" wrote:
> >
> > > Sorry that was my fault, I should have said
> > > right click 'This workbook'
> > > Insert module and paste the code into the new module.
> > >
> > > Mike
> > >
> > > "Gemz" wrote:
> > >
> > > > I tried that but get a big X and '400' error!
> > > >
> > > > i selected 'this workbook' from the left hand side somwhere and pasted the
> > > > code there.
> > > >
> > > > please advise..
> > > >
> > > > "Mike H" wrote:
> > > >
> > > > > Missed you wanted a space so substitute this line
> > > > >
> > > > > c.Offset(0, 2).Value = c.Value & " , " & c.Offset(0, 1).Value
> > > > >
> > > > > Mike
> > > > >
> > > > > "Gemz" wrote:
> > > > >
> > > > > > I would like columns C & D to concatenate with a space and a comma seperating
> > > > > > the two words in each of my many sheets. the columns remain the same all the
> > > > > > time.
> > > > > >
> > > > > > How do i do this? i tried using the concatenate formula in my macro code but
> > > > > > it didnt work as i dont really know how to refer to all sheets. Ideally i
> > > > > > would like a click of a button to concatenate C & D (with comma and space) in
> > > > > > each of my sheets.
> > > > > >
> > > > > > thanks.

Try

Application.screenupdating=false

the code

Application.screenupdating=true


"Gemz" wrote:

> thanks it works now but it takes a while to run.. there are like 20 sheets
> but isnt there a way to speed up?
>
> thanks.
>
> "Mike H" wrote:
>
> > Gemz,
> >
> > I can't replicate that error and no it doesn't matter what your sheets are
> > called. Looking at the code again it doesn't really matter if its in a
> > module or a worksheet it should still work. I am confused by your comment
> > that 'I have the workbook open'. You have pasted the code into the workbook
> > you want to concatenate haven't you
> >
> > Mike
> >
> > "Gemz" wrote:
> >
> > > I now get a run time error 1004 and it highlights "ws.Select".
> > >
> > > i have the workbook open, is there something im doing wrong? does it matter
> > > if each worksheet is named and not called sheet..?
> > >
> > > sorry about this!
> > >
> > > thanks for help
> > >
> > > "Mike H" wrote:
> > >
> > > > Sorry that was my fault, I should have said
> > > > right click 'This workbook'
> > > > Insert module and paste the code into the new module.
> > > >
> > > > Mike
> > > >
> > > > "Gemz" wrote:
> > > >
> > > > > I tried that but get a big X and '400' error!
> > > > >
> > > > > i selected 'this workbook' from the left hand side somwhere and pasted the
> > > > > code there.
> > > > >
> > > > > please advise..
> > > > >
> > > > > "Mike H" wrote:
> > > > >
> > > > > > Missed you wanted a space so substitute this line
> > > > > >
> > > > > > c.Offset(0, 2).Value = c.Value & " , " & c.Offset(0, 1).Value
> > > > > >
> > > > > > Mike
> > > > > >
> > > > > > "Gemz" wrote:
> > > > > >
> > > > > > > I would like columns C & D to concatenate with a space and a comma seperating
> > > > > > > the two words in each of my many sheets. the columns remain the same all the
> > > > > > > time.
> > > > > > >
> > > > > > > How do i do this? i tried using the concatenate formula in my macro code but
> > > > > > > it didnt work as i dont really know how to refer to all sheets. Ideally i
> > > > > > > would like a click of a button to concatenate C & D (with comma and space) in
> > > > > > > each of my sheets.
> > > > > > >
> > > > > > > thanks.

This is how my code looks like now, it still took 2-3mins to run and froze
all other excel applications..

Sub concatenate1()
Application.ScreenUpdating = False
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Select
Lastrow = Range("C65536").End(xlUp).Row
Set myrange = Range("C1:C" & Lastrow)
For Each c In myrange
c.Offset(0, 2).Value = c.Value & " , " & c.Offset(0, 1).Value
Application.ScreenUpdating = True

Next

Next ws
End Sub


thanks.

"Mike H" wrote:

> Try
>
> Application.screenupdating=false
>
> the code
>
> Application.screenupdating=true
>
>
> "Gemz" wrote:
>
> > thanks it works now but it takes a while to run.. there are like 20 sheets
> > but isnt there a way to speed up?
> >
> > thanks.
> >
> > "Mike H" wrote:
> >
> > > Gemz,
> > >
> > > I can't replicate that error and no it doesn't matter what your sheets are
> > > called. Looking at the code again it doesn't really matter if its in a
> > > module or a worksheet it should still work. I am confused by your comment
> > > that 'I have the workbook open'. You have pasted the code into the workbook
> > > you want to concatenate haven't you
> > >
> > > Mike
> > >
> > > "Gemz" wrote:
> > >
> > > > I now get a run time error 1004 and it highlights "ws.Select".
> > > >
> > > > i have the workbook open, is there something im doing wrong? does it matter
> > > > if each worksheet is named and not called sheet..?
> > > >
> > > > sorry about this!
> > > >
> > > > thanks for help
> > > >
> > > > "Mike H" wrote:
> > > >
> > > > > Sorry that was my fault, I should have said
> > > > > right click 'This workbook'
> > > > > Insert module and paste the code into the new module.
> > > > >
> > > > > Mike
> > > > >
> > > > > "Gemz" wrote:
> > > > >
> > > > > > I tried that but get a big X and '400' error!
> > > > > >
> > > > > > i selected 'this workbook' from the left hand side somwhere and pasted the
> > > > > > code there.
> > > > > >
> > > > > > please advise..
> > > > > >
> > > > > > "Mike H" wrote:
> > > > > >
> > > > > > > Missed you wanted a space so substitute this line
> > > > > > >
> > > > > > > c.Offset(0, 2).Value = c.Value & " , " & c.Offset(0, 1).Value
> > > > > > >
> > > > > > > Mike
> > > > > > >
> > > > > > > "Gemz" wrote:
> > > > > > >
> > > > > > > > I would like columns C & D to concatenate with a space and a comma seperating
> > > > > > > > the two words in each of my many sheets. the columns remain the same all the
> > > > > > > > time.
> > > > > > > >
> > > > > > > > How do i do this? i tried using the concatenate formula in my macro code but
> > > > > > > > it didnt work as i dont really know how to refer to all sheets. Ideally i
> > > > > > > > would like a click of a button to concatenate C & D (with comma and space) in
> > > > > > > > each of my sheets.
> > > > > > > >
> > > > > > > > thanks.