Copy Data from Multiple Columns to a Single Column

[Sufiyan97]

I have data set in Columns H, I and J, and I want to copy all the data in a single column.
Expected result in column G

 

[mumps]

Try:

Sub CopyCols()
    Application.ScreenUpdating = False
    Dim LastRow As Long, x As Long
    LastRow = Range("H" & Rows.Count).End(xlUp).Row
    For x = 8 To 10
        Cells(1, x).Resize(LastRow).Copy Cells(Rows.Count, "A").End(xlUp).Offset(1)
    Next x
    Range("A1").Delete
    Application.ScreenUpdating = True
End Sub

 

[Sufiyan97]

Try:

Sub CopyCols()
    Application.ScreenUpdating = False
    Dim LastRow As Long, x As Long
    LastRow = Range("H" & Rows.Count).End(xlUp).Row
    For x = 8 To 10
        Cells(1, x).Resize(LastRow).Copy Cells(Rows.Count, "A").End(xlUp).Offset(1)
    Next x
    Range("A1").Delete
    Application.ScreenUpdating = True
End Sub

Thank you very much! It works perfectly.

Is there any solution without VBA....????

 

[sandy666]

with Power Query (Get&Transform) built in XL2016

Column1	Column2	Column3		Column1
A	E	I		A
B	F	J		B
C	G	K		C
D	H	L		D
				E
				F
				G
				H
				I
				J
				K
				L

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    T2C = Table.ToColumns(Source),
    C2T = Table.FromList(T2C, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Expand = Table.ExpandListColumn(C2T, "Column1")
in
    Expand

 

[mumps]

You are very welcome. I think that you would need to use the macro or the Power Query suggested by @sandy666.

 

[sandy666]

if you don't care: by rows

Book1
	A	B	C	D
1	A	A	E	I
2	E	B	F	J
3	I	C	G	K
4	B	D	H	L
5	F
6	J
7	C
8	G
9	K
10	D
11	H
12	L
Sheet4

Cell Formulas
Range	Formula
A1:A12	A1	=IFERROR(INDEX($B$1:$D$4,INT((ROWS($B$1:$B1)-1)/COLUMNS($B$1:$D$4))+1,MOD(ROWS($B$1:$B1)-1,COLUMNS($B$1:$D$4))+1),"")

maybe someone else will give you solution by columns

 

[Fluff]

To do it by columns

+Fluff v2.xlsm
	G	H	I	J
1	A	A	E	I
2	B	B	F	J
3	C	C	G	K
4	D	D	H	L
5	E
6	F
7	G
8	H
9	I
10	J
11	K
12	L
13
Main

Cell Formulas
Range	Formula
G1:G13	G1	=IFERROR(INDEX($H$1:$J$4,MOD(ROWS($H$1:$H1)-1,ROWS($H$1:$J$4))+1,INT((ROWS($H$1:$H1)-1)/ROWS($H$1:$J$4))+1),"")

 

[Sufiyan97]

with Power Query (Get&Transform) built in XL2016

Column1	Column2	Column3		Column1
A	E	I		A
B	F	J		B
C	G	K		C
D	H	L		D
				E
				F
				G
				H
				I
				J
				K
				L

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    T2C = Table.ToColumns(Source),
    C2T = Table.FromList(T2C, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Expand = Table.ExpandListColumn(C2T, "Column1")
in
    Expand

Thank you very much for your help!

 

[Sufiyan97]

if you don't care: by rows

Book1
	A	B	C	D
1	A	A	E	I
2	E	B	F	J
3	I	C	G	K
4	B	D	H	L
5	F
6	J
7	C
8	G
9	K
10	D
11	H
12	L
Sheet4

Cell Formulas
Range	Formula
A1:A12	A1	=IFERROR(INDEX($B$1:$D$4,INT((ROWS($B$1:$B1)-1)/COLUMNS($B$1:$D$4))+1,MOD(ROWS($B$1:$B1)-1,COLUMNS($B$1:$D$4))+1),"")

maybe someone else will give you solution by columns

Thank you again for your time and support!

 

[sandy666]

You are welcome
Thanks for the feedback