Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to concatenate in macro across sheets
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to concatenate in macro across sheets
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to concatenate in macro across sheets
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to concatenate in macro across sheets
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to concatenate in macro across sheets
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to concatenate in macro across sheets
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to concatenate in macro across sheets
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to concatenate in macro across sheets
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to concatenate in macro across sheets
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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to concatenate in macro across sheets
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. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to concatenate in macro across sheets
Gemz,
I have a run of the mill laptop and my machine concatenated 100k records across 3 worksheets in 12 seconds so it's one of a few things:- You have a very large amount of records Your PC processor time is being divided between this and other tasks Your PC is slow I don't see how the code can be significantly improved. Mike "Gemz" wrote: 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. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to concatenate in macro across sheets
yes there is quite a large amount of data.
ok thanks for helping. "Mike H" wrote: Gemz, I have a run of the mill laptop and my machine concatenated 100k records across 3 worksheets in 12 seconds so it's one of a few things:- You have a very large amount of records Your PC processor time is being divided between this and other tasks Your PC is slow I don't see how the code can be significantly improved. Mike "Gemz" wrote: 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. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to concatenate in macro across sheets
Sorry to bother you again but i have a quick question about the below, would
the macro work fine if the workbook is emailed across to someone else for them to use the macro? they would probably assign the macro to a toolbar button.. obviously the workbook would need to be opened for the macro to work? thanks. "Gemz" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Concatenate using Macro | Excel Discussion (Misc queries) | |||
Concatenate Column C in multiple sheets into single sheet. | Excel Discussion (Misc queries) | |||
concatenate with more than 10 sheets | Excel Discussion (Misc queries) | |||
Concatenate Macro | Excel Programming | |||
Concatenate Macro | Excel Programming |