ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Copy 3 columns become one column with macro (https://www.excelbanter.com/excel-programming/416633-copy-3-columns-become-one-column-macro.html)

Lyn
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

joel
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

Lyn
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

joel
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

Lyn
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

joel
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

Lyn
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


All times are GMT +1. The time now is 03:33 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com