Copy 3 columns become one column with macro

L

Lyn

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
 
J

Joel

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
 
L

Lyn

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
 
J

Joel

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
 
L

Lyn

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
 
J

Joel

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
 
L

Lyn

Hi Joel,

Perfect, thanks a lot

regards
lyn

Joel said:
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
Click to expand...
 
Top