| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy 3 columns become one column with macro
Dear Expert,
I have 3 columns data (for example column A, B and C) and I would like copy to become one column data only, the example as below: A B C D Tomato 1 a Tomato Sugar 2 b Sugar 3 c 1 d 2 3 a b c d Could you please advice me Regards Lyn |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy 3 columns become one column with macro
Sub CombineColumns()
'Copy column A to Column d Columns("A").Copy Destination:=Columns("D") NewRowD = Range("D" & Rows.Count).End(xlUp).Row + 1 LastRowB = Range("B" & Rows.Count).End(xlUp).Row Set CopyRange = Range("B1:B" & LastRowB) CopyRange.Copy Destination:=Range("D" & NewRowD) NewRowD = Range("D" & Rows.Count).End(xlUp).Row + 1 LastRowC = Range("C" & Rows.Count).End(xlUp).Row Set CopyRange = Range("C1:C" & LastRowC) CopyRange.Copy Destination:=Range("D" & NewRowD) End Sub "Lyn" wrote: Dear Expert, I have 3 columns data (for example column A, B and C) and I would like copy to become one column data only, the example as below: A B C D Tomato 1 a Tomato Sugar 2 b Sugar 3 c 1 d 2 3 a b c d Could you please advice me Regards Lyn |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy 3 columns become one column with macro
HI Joel,
This is perfect formula, and thanks a lot. If you not mind, I have another question. I would like put data for example in column A and in the column D will be automatically update also. Cause with this formula I have to close my excel file and open again to update data in column D Could you please advice this one. Regards lyn "Joel" wrote: Sub CombineColumns() 'Copy column A to Column d Columns("A").Copy Destination:=Columns("D") NewRowD = Range("D" & Rows.Count).End(xlUp).Row + 1 LastRowB = Range("B" & Rows.Count).End(xlUp).Row Set CopyRange = Range("B1:B" & LastRowB) CopyRange.Copy Destination:=Range("D" & NewRowD) NewRowD = Range("D" & Rows.Count).End(xlUp).Row + 1 LastRowC = Range("C" & Rows.Count).End(xlUp).Row Set CopyRange = Range("C1:C" & LastRowC) CopyRange.Copy Destination:=Range("D" & NewRowD) End Sub "Lyn" wrote: Dear Expert, I have 3 columns data (for example column A, B and C) and I would like copy to become one column data only, the example as below: A B C D Tomato 1 a Tomato Sugar 2 b Sugar 3 c 1 d 2 3 a b c d Could you please advice me Regards Lyn |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy 3 columns become one column with macro
You could make the code a worksheet change and clear column D before the code
is run Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False Columns("D").ClearContents 'Copy column A to Column d Columns("A").Copy Destination:=Columns("D") NewRowD = Range("D" & Rows.Count).End(xlUp).Row + 1 LastRowB = Range("B" & Rows.Count).End(xlUp).Row Set CopyRange = Range("B1:B" & LastRowB) CopyRange.Copy Destination:=Range("D" & NewRowD) NewRowD = Range("D" & Rows.Count).End(xlUp).Row + 1 LastRowC = Range("C" & Rows.Count).End(xlUp).Row Set CopyRange = Range("C1:C" & LastRowC) CopyRange.Copy Destination:=Range("D" & NewRowD) Application.EnableEvents = True End Sub "Lyn" wrote: HI Joel, This is perfect formula, and thanks a lot. If you not mind, I have another question. I would like put data for example in column A and in the column D will be automatically update also. Cause with this formula I have to close my excel file and open again to update data in column D Could you please advice this one. Regards lyn "Joel" wrote: Sub CombineColumns() 'Copy column A to Column d Columns("A").Copy Destination:=Columns("D") NewRowD = Range("D" & Rows.Count).End(xlUp).Row + 1 LastRowB = Range("B" & Rows.Count).End(xlUp).Row Set CopyRange = Range("B1:B" & LastRowB) CopyRange.Copy Destination:=Range("D" & NewRowD) NewRowD = Range("D" & Rows.Count).End(xlUp).Row + 1 LastRowC = Range("C" & Rows.Count).End(xlUp).Row Set CopyRange = Range("C1:C" & LastRowC) CopyRange.Copy Destination:=Range("D" & NewRowD) End Sub "Lyn" wrote: Dear Expert, I have 3 columns data (for example column A, B and C) and I would like copy to become one column data only, the example as below: A B C D Tomato 1 a Tomato Sugar 2 b Sugar 3 c 1 d 2 3 a b c d Could you please advice me Regards Lyn |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy 3 columns become one column with macro
HI Joel,
This greats... when i put data in column B or C, data from column A on column D disappear. I have to save and close from xls sheet to update my data. Please advice again. Regards lyn "Joel" wrote: You could make the code a worksheet change and clear column D before the code is run Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False Columns("D").ClearContents 'Copy column A to Column d Columns("A").Copy Destination:=Columns("D") NewRowD = Range("D" & Rows.Count).End(xlUp).Row + 1 LastRowB = Range("B" & Rows.Count).End(xlUp).Row Set CopyRange = Range("B1:B" & LastRowB) CopyRange.Copy Destination:=Range("D" & NewRowD) NewRowD = Range("D" & Rows.Count).End(xlUp).Row + 1 LastRowC = Range("C" & Rows.Count).End(xlUp).Row Set CopyRange = Range("C1:C" & LastRowC) CopyRange.Copy Destination:=Range("D" & NewRowD) Application.EnableEvents = True End Sub "Lyn" wrote: HI Joel, This is perfect formula, and thanks a lot. If you not mind, I have another question. I would like put data for example in column A and in the column D will be automatically update also. Cause with this formula I have to close my excel file and open again to update data in column D Could you please advice this one. Regards lyn "Joel" wrote: Sub CombineColumns() 'Copy column A to Column d Columns("A").Copy Destination:=Columns("D") NewRowD = Range("D" & Rows.Count).End(xlUp).Row + 1 LastRowB = Range("B" & Rows.Count).End(xlUp).Row Set CopyRange = Range("B1:B" & LastRowB) CopyRange.Copy Destination:=Range("D" & NewRowD) NewRowD = Range("D" & Rows.Count).End(xlUp).Row + 1 LastRowC = Range("C" & Rows.Count).End(xlUp).Row Set CopyRange = Range("C1:C" & LastRowC) CopyRange.Copy Destination:=Range("D" & NewRowD) End Sub "Lyn" wrote: Dear Expert, I have 3 columns data (for example column A, B and C) and I would like copy to become one column data only, the example as below: A B C D Tomato 1 a Tomato Sugar 2 b Sugar 3 c 1 d 2 3 a b c d Could you please advice me Regards Lyn |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy 3 columns become one column with macro
Events are somehow getting disabled. Try the code below to re-enable Events
without closing the book. Are there any other macros that can be failing? Cechk your settings in VBA Tools - OPtions - General - Error Trapping. Set to break on All Errors. Sub EnableEvent() Application.EnableEvents = True End Sub "Lyn" wrote: HI Joel, This greats... when i put data in column B or C, data from column A on column D disappear. I have to save and close from xls sheet to update my data. Please advice again. Regards lyn "Joel" wrote: You could make the code a worksheet change and clear column D before the code is run Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False Columns("D").ClearContents 'Copy column A to Column d Columns("A").Copy Destination:=Columns("D") NewRowD = Range("D" & Rows.Count).End(xlUp).Row + 1 LastRowB = Range("B" & Rows.Count).End(xlUp).Row Set CopyRange = Range("B1:B" & LastRowB) CopyRange.Copy Destination:=Range("D" & NewRowD) NewRowD = Range("D" & Rows.Count).End(xlUp).Row + 1 LastRowC = Range("C" & Rows.Count).End(xlUp).Row Set CopyRange = Range("C1:C" & LastRowC) CopyRange.Copy Destination:=Range("D" & NewRowD) Application.EnableEvents = True End Sub "Lyn" wrote: HI Joel, This is perfect formula, and thanks a lot. If you not mind, I have another question. I would like put data for example in column A and in the column D will be automatically update also. Cause with this formula I have to close my excel file and open again to update data in column D Could you please advice this one. Regards lyn "Joel" wrote: Sub CombineColumns() 'Copy column A to Column d Columns("A").Copy Destination:=Columns("D") NewRowD = Range("D" & Rows.Count).End(xlUp).Row + 1 LastRowB = Range("B" & Rows.Count).End(xlUp).Row Set CopyRange = Range("B1:B" & LastRowB) CopyRange.Copy Destination:=Range("D" & NewRowD) NewRowD = Range("D" & Rows.Count).End(xlUp).Row + 1 LastRowC = Range("C" & Rows.Count).End(xlUp).Row Set CopyRange = Range("C1:C" & LastRowC) CopyRange.Copy Destination:=Range("D" & NewRowD) End Sub "Lyn" wrote: Dear Expert, I have 3 columns data (for example column A, B and C) and I would like copy to become one column data only, the example as below: A B C D Tomato 1 a Tomato Sugar 2 b Sugar 3 c 1 d 2 3 a b c d Could you please advice me Regards Lyn |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy 3 columns become one column with macro
Hi Joel,
Perfect, thanks a lot regards lyn "Joel" wrote: Events are somehow getting disabled. Try the code below to re-enable Events without closing the book. Are there any other macros that can be failing? Cechk your settings in VBA Tools - OPtions - General - Error Trapping. Set to break on All Errors. Sub EnableEvent() Application.EnableEvents = True End Sub "Lyn" wrote: HI Joel, This greats... when i put data in column B or C, data from column A on column D disappear. I have to save and close from xls sheet to update my data. Please advice again. Regards lyn "Joel" wrote: You could make the code a worksheet change and clear column D before the code is run Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False Columns("D").ClearContents 'Copy column A to Column d Columns("A").Copy Destination:=Columns("D") NewRowD = Range("D" & Rows.Count).End(xlUp).Row + 1 LastRowB = Range("B" & Rows.Count).End(xlUp).Row Set CopyRange = Range("B1:B" & LastRowB) CopyRange.Copy Destination:=Range("D" & NewRowD) NewRowD = Range("D" & Rows.Count).End(xlUp).Row + 1 LastRowC = Range("C" & Rows.Count).End(xlUp).Row Set CopyRange = Range("C1:C" & LastRowC) CopyRange.Copy Destination:=Range("D" & NewRowD) Application.EnableEvents = True End Sub "Lyn" wrote: HI Joel, This is perfect formula, and thanks a lot. If you not mind, I have another question. I would like put data for example in column A and in the column D will be automatically update also. Cause with this formula I have to close my excel file and open again to update data in column D Could you please advice this one. Regards lyn "Joel" wrote: Sub CombineColumns() 'Copy column A to Column d Columns("A").Copy Destination:=Columns("D") NewRowD = Range("D" & Rows.Count).End(xlUp).Row + 1 LastRowB = Range("B" & Rows.Count).End(xlUp).Row Set CopyRange = Range("B1:B" & LastRowB) CopyRange.Copy Destination:=Range("D" & NewRowD) NewRowD = Range("D" & Rows.Count).End(xlUp).Row + 1 LastRowC = Range("C" & Rows.Count).End(xlUp).Row Set CopyRange = Range("C1:C" & LastRowC) CopyRange.Copy Destination:=Range("D" & NewRowD) End Sub "Lyn" wrote: Dear Expert, I have 3 columns data (for example column A, B and C) and I would like copy to become one column data only, the example as below: A B C D Tomato 1 a Tomato Sugar 2 b Sugar 3 c 1 d 2 3 a b c d Could you please advice me Regards Lyn |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro to copy and paste values (columns)I have a macro file built | Excel Programming | |||
| How to copy data from different columns in an Excel to one column | Excel Programming | |||
| copy text in multiple columns into one column | Excel Discussion (Misc queries) | |||
| copy two columns without blanks in the first column | Excel Programming | |||
| Determine last row in columns 1-4 to copy a formula in column 5 | Excel Programming | |||
Linear Mode
