Copy selected column in range to another sheet

[motilulla]

Using Excel 2010
Hello,

I need VBA which can have column with range selection via input box and then copy columns in to another sheet

I have sheet (data) from this sheet I want to copy (3 columns) column C from range C5:C45, column F from range F5:F45, column J from range J5:J45 in to sheet (copy columns).

For example VBA give an option to select column if I select C5, F5 and J5, it should copy 3 columns with range from 5 to 45 as shown in the example below.

data sheet...

Copy Columns.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2
3
4
5	S.N	Group	n1	n2	n3	n4	n5	n6	n7	n8	n9	n10
6	1	1	27	30	32	29	38	26	36	11	13	44
7	2	2	4	42	38	29	26	43	31	7	47	39
8	3	3	44	9	35	6	17	33	34	38	29	33
9	4	4	46	48	27	28	38	47	28	11	29	44
10	5	1	36	9	34	36	38	16	12	31	42	4
11	6	2	34	47	7	5	10	11	27	40	10	22
12	7	3	43	40	50	30	38	2	46	1	4	28
13	8	4	26	31	8	44	2	26	5	14	17	31
14	9	1	3	12	26	8	18	13	2	31	37	46
15	10	2	38	23	26	1	9	48	3	15	30	14
16	11	3	9	44	28	16	3	33	3	45	43	6
17	12	4	32	41	11	31	1	47	3	49	39	33
18	13	1	13	35	11	17	39	30	18	24	12	27
19	14	2	2	46	34	26	26	45	19	44	50	14
20	15	3	23	30	9	49	30	30	7	19	16	44
21	16	4	22	35	38	20	27	24	40	20	47	46
22	17	1	16	50	46	37	21	45	11	40	50	17
23	18	2	5	1	48	25	50	40	23	42	34	29
24	19	3	16	33	36	29	20	46	20	44	33	25
25	20	4	24	23	13	32	48	15	14	2	46	18
26	21	1	19	46	4	9	16	37	4	13	38	44
27	22	2	26	22	33	34	31	42	2	4	6	4
28	23	3	34	21	17	29	44	23	7	49	10	13
29	24	4	35	5	14	4	10	30	44	12	29	42
30	25	1	27	50	43	28	1	38	33	5	12	40
31	26	2	20	19	32	38	43	40	17	24	36	45
32	27	3	45	42	4	4	32	18	19	29	33	14
33	28	4	43	45	24	38	7	44	45	42	17	20
34	29	1	45	10	13	50	36	45	34	29	32	41
35	30	2	38	5	23	49	50	22	25	12	23	8
36	31	3	22	36	17	25	12	10	27	39	40	46
37	32	4
38	33	1
39	34	2
40	35	3
41	36	4
42	37	1
43	38	2
44	39	3
45	40	4
Data

Copy Here.....

Copy Columns.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2
3
4
5	S.N	Group	n1	n4	n8
6	1	1	27	29	11
7	2	2	4	29	7
8	3	3	44	6	38
9	4	4	46	28	11
10	5	1	36	36	31
11	6	2	34	5	40
12	7	3	43	30	1
13	8	4	26	44	14
14	9	1	3	8	31
15	10	2	38	1	15
16	11	3	9	16	45
17	12	4	32	31	49
18	13	1	13	17	24
19	14	2	2	26	44
20	15	3	23	49	19
21	16	4	22	20	20
22	17	1	16	37	40
23	18	2	5	25	42
24	19	3	16	29	44
25	20	4	24	32	2
26	21	1	19	9	13
27	22	2	26	34	4
28	23	3	34	29	49
29	24	4	35	4	12
30	25	1	27	28	5
31	26	2	20	38	24
32	27	3	45	4	29
33	28	4	43	38	42
34	29	1	45	50	29
35	30	2	38	49	12
36	31	3	22	25	39
37	32	4
38	33	1
39	34	2
40	35	3
41	36	4
42	37	1
43	38	2
44	39	3
45	40	4
Copy Column

Regards,
Moti

 

[HighAndWilder]

Using Excel 2010
Hello,

I need VBA which can have column with range selection via input box and then copy columns in to another sheet

I have sheet (data) from this sheet I want to copy (3 columns) column C from range C5:C45, column F from range F5:F45, column J from range J5:J45 in to sheet (copy columns).

For example VBA give an option to select column if I select C5, F5 and J5, it should copy 3 columns with range from 5 to 45 as shown in the example below.

data sheet...

Copy Columns.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2
3
4
5	S.N	Group	n1	n2	n3	n4	n5	n6	n7	n8	n9	n10
6	1	1	27	30	32	29	38	26	36	11	13	44
7	2	2	4	42	38	29	26	43	31	7	47	39
8	3	3	44	9	35	6	17	33	34	38	29	33
9	4	4	46	48	27	28	38	47	28	11	29	44
10	5	1	36	9	34	36	38	16	12	31	42	4
11	6	2	34	47	7	5	10	11	27	40	10	22
12	7	3	43	40	50	30	38	2	46	1	4	28
13	8	4	26	31	8	44	2	26	5	14	17	31
14	9	1	3	12	26	8	18	13	2	31	37	46
15	10	2	38	23	26	1	9	48	3	15	30	14
16	11	3	9	44	28	16	3	33	3	45	43	6
17	12	4	32	41	11	31	1	47	3	49	39	33
18	13	1	13	35	11	17	39	30	18	24	12	27
19	14	2	2	46	34	26	26	45	19	44	50	14
20	15	3	23	30	9	49	30	30	7	19	16	44
21	16	4	22	35	38	20	27	24	40	20	47	46
22	17	1	16	50	46	37	21	45	11	40	50	17
23	18	2	5	1	48	25	50	40	23	42	34	29
24	19	3	16	33	36	29	20	46	20	44	33	25
25	20	4	24	23	13	32	48	15	14	2	46	18
26	21	1	19	46	4	9	16	37	4	13	38	44
27	22	2	26	22	33	34	31	42	2	4	6	4
28	23	3	34	21	17	29	44	23	7	49	10	13
29	24	4	35	5	14	4	10	30	44	12	29	42
30	25	1	27	50	43	28	1	38	33	5	12	40
31	26	2	20	19	32	38	43	40	17	24	36	45
32	27	3	45	42	4	4	32	18	19	29	33	14
33	28	4	43	45	24	38	7	44	45	42	17	20
34	29	1	45	10	13	50	36	45	34	29	32	41
35	30	2	38	5	23	49	50	22	25	12	23	8
36	31	3	22	36	17	25	12	10	27	39	40	46
37	32	4
38	33	1
39	34	2
40	35	3
41	36	4
42	37	1
43	38	2
44	39	3
45	40	4
Data

Copy Here.....

Copy Columns.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2
3
4
5	S.N	Group	n1	n4	n8
6	1	1	27	29	11
7	2	2	4	29	7
8	3	3	44	6	38
9	4	4	46	28	11
10	5	1	36	36	31
11	6	2	34	5	40
12	7	3	43	30	1
13	8	4	26	44	14
14	9	1	3	8	31
15	10	2	38	1	15
16	11	3	9	16	45
17	12	4	32	31	49
18	13	1	13	17	24
19	14	2	2	26	44
20	15	3	23	49	19
21	16	4	22	20	20
22	17	1	16	37	40
23	18	2	5	25	42
24	19	3	16	29	44
25	20	4	24	32	2
26	21	1	19	9	13
27	22	2	26	34	4
28	23	3	34	29	49
29	24	4	35	4	12
30	25	1	27	28	5
31	26	2	20	38	24
32	27	3	45	4	29
33	28	4	43	38	42
34	29	1	45	50	29
35	30	2	38	49	12
36	31	3	22	25	39
37	32	4
38	33	1
39	34	2
40	35	3
41	36	4
42	37	1
43	38	2
44	39	3
45	40	4
Copy Column

Regards,
Moti

Try this.

You will be prompted to select just the cells in the headers of the columns that you want to copy. e,g, A5:B5, D5,G5

You can select non-contiguous cells by holding down the Ctrl key.

You will then be prompted to select the destination cell that can be on a diffrent sheet.

Test it on a copy of your data.

Public Sub subCopyColumns()
Dim rng As Range
Dim r As Range
Dim t As String
Dim rngToCopy As Range
Dim rngDestination As Range

  ActiveWorkbook.Save
    
  On Error Resume Next
    
  Set rng = Application.InputBox(Prompt:="Select headings of columns to copy.", Type:=8)
  
  On Error GoTo 0
  
  If rng Is Nothing Then
    Exit Sub
  End If
    
  For Each r In rng.Cells
    If Not rngToCopy Is Nothing Then
      Set rngToCopy = Union(rngToCopy, rng.CurrentRegion.Columns(r.Column))
    Else
      Set rngToCopy = rng.CurrentRegion.Columns(r.Column)
    End If
  Next r
  
  On Error Resume Next
  
  Set rngDestination = Application.InputBox(Prompt:="Select destination cell.", Type:=8)
   
  On Error GoTo 0
  
  If Not rngDestination Is Nothing Then
  
    Worksheets(rngDestination.Parent.Name).Activate
  
    For Each rng In rngToCopy.Areas
            
      rng.Copy rngDestination
    
      Set rngDestination = rngDestination.Offset(, rng.Columns.Count)
    
    Next rng
  
  End If
  
  ActiveWorkbook.Save
  
End Sub

 

[motilulla]

Try this.

You will be prompted to select just the cells in the headers of the columns that you want to copy. e,g, A5:B5, D5,G5

You can select non-contiguous cells by holding down the Ctrl key.

You will then be prompted to select the destination cell that can be on a diffrent sheet.

Test it on a copy of your data.

Hello HighAndWilder, thank you for the help.

1st-VBA is copying selection of columns in the (CurrentRegion) from row 1 to bottom entire column, does it is possible to set range row 5 to below row 45 only

2nd-instead of selecting can be paste always in the destination sheet from C5 to right and below?

Kind regards,
Moti

 

[HighAndWilder]

Hello HighAndWilder, thank you for the help.

1st-VBA is copying selection of columns in the (CurrentRegion) from row 1 to bottom entire column, does it is possible to set range row 5 to below row 45 only

2nd-instead of selecting can be paste always in the destination sheet from C5 to right and below?

Kind regards,
Moti

Anything is possible.

Regarding :
1st-VBA is copying selection of columns in the (CurrentRegion) from row 1 to bottom entire column, does it is possible to set range row 5 to below row 45 only

So just rows 5 to 45 inclusive then?

Regarding :
2nd-instead of selecting can be paste always in the destination sheet from C5 to right and below?

That is possible but will the destination sheet always be 'copy columns'?

What is to be done with any existing data in the destination sheet?

 

[motilulla]

Anything is possible.

Hello HighAndWilder, that is great thank for the question.

So just rows 5 to 45 inclusive then?

Yes it is correct 5 to 45 inclusive

Regarding :
2nd-instead of selecting can be paste always in the destination sheet from C5 to right and below?

That is possible but will the destination sheet always be 'copy columns'?

Yes the destination sheet always be 'copy columns' this is correct

What is to be done with any existing data in the destination sheet?

They will remain as it is in the destination sheet.

Kind regards,
Moti

 

[HighAndWilder]

Hello HighAndWilder, that is great thank for the question.

Yes it is correct 5 to 45 inclusive

Yes the destination sheet always be 'copy columns' this is correct

They will remain as it is in the destination sheet.

Kind regards,
Moti

Public Sub subCopyColumns()
Dim rng As Range
Dim r As Range
Dim t As String
Dim rngToCopy As Range
Dim rngDestination As Range

  ActiveWorkbook.Save
    
  On Error Resume Next
    
  Set rng = Application.InputBox(Prompt:="Select headings of columns to copy.", Type:=8)
  
  On Error GoTo 0
  
  If rng Is Nothing Then
    Exit Sub
  End If
    
  For Each r In rng.Cells
    If Not rngToCopy Is Nothing Then
      Set rngToCopy = Union(rngToCopy, rng.CurrentRegion.Columns(r.Column).Resize(41, r.Columns.Count))
    Else
      Set rngToCopy = rng.CurrentRegion.Columns(r.Column).Resize(41, r.Columns.Count)
    End If
  Next r
  
  Worksheets("copy columns").Activate
    
  Set rngDestination = Worksheets("copy columns").Range("C5")
  
  For Each rng In rngToCopy.Areas
            
    rng.Copy rngDestination
    
    Set rngDestination = rngDestination.Offset(, rng.Columns.Count)
    
  Next rng
  
  ActiveWorkbook.Save
  
End Sub

 

[motilulla]

Public Sub subCopyColumns()

Hello HighAndWilder, it is almost perfect. Only if I fill in the data sheet row A1:L4 with some text or numbers and run the code pop up input box where I select example C5, G5 and P5 (it must select the column from rows 5 to 45 inclusive)

But it is copying to destination sheet in cell C5 correctly (but coping row from 1 to 45 of the data sheet) which must be also 5 to 45…please can you take a look sorry to bother you.

Kind regards,
Moti

 

[HighAndWilder]

Hello HighAndWilder, it is almost perfect. Only if I fill in the data sheet row A1:L4 with some text or numbers and run the code pop up input box where I select example C5, G5 and P5 (it must select the column from rows 5 to 45 inclusive)

But it is copying to destination sheet in cell C5 correctly (but coping row from 1 to 45 of the data sheet) which must be also 5 to 45…please can you take a look sorry to bother you.

Kind regards,
Moti

Can you post a copy of the data in 'copy columns' after the copy using XL2BB. Thanks.

 

[motilulla]

Can you post a copy of the data in 'copy columns' after the copy using XL2BB. Thanks.

Hello HighAndWilder, here is the new data sheet layout and copy results in the sheet copy columns (you will see it is coping data from row1) please check and also if it is not much please can it is possible to paste values only. Thank you

Data sheet....

Copy Columns.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	S.N	Group	n1	n2	n3	n4	n5	n6	n7	n8	n9	n10
2	S.N	Group	n1	n2	n3	n4	n5	n6	n7	n8	n9	n10
3	S.N	Group	n1	n2	n3	n4	n5	n6	n7	n8	n9	n10
4	S.N	Group	n1	n2	n3	n4	n5	n6	n7	n8	n9	n10
5	S.N	Group	n1	n2	n3	n4	n5	n6	n7	n8	n9	n10
6	1	1	27	30	32	29	38	26	36	11	13	44
7	2	2	4	42	38	29	26	43	31	7	47	39
8	3	3	44	9	35	6	17	33	34	38	29	33
9	4	4	46	48	27	28	38	47	28	11	29	44
10	5	1	36	9	34	36	38	16	12	31	42	4
11	6	2	34	47	7	5	10	11	27	40	10	22
12	7	3	43	40	50	30	38	2	46	1	4	28
13	8	4	26	31	8	44	2	26	5	14	17	31
14	9	1	3	12	26	8	18	13	2	31	37	46
15	10	2	38	23	26	1	9	48	3	15	30	14
16	11	3	9	44	28	16	3	33	3	45	43	6
17	12	4	32	41	11	31	1	47	3	49	39	33
18	13	1	13	35	11	17	39	30	18	24	12	27
19	14	2	2	46	34	26	26	45	19	44	50	14
20	15	3	23	30	9	49	30	30	7	19	16	44
21	16	4	22	35	38	20	27	24	40	20	47	46
22	17	1	16	50	46	37	21	45	11	40	50	17
23	18	2	5	1	48	25	50	40	23	42	34	29
24	19	3	16	33	36	29	20	46	20	44	33	25
25	20	4	24	23	13	32	48	15	14	2	46	18
26	21	1	19	46	4	9	16	37	4	13	38	44
27	22	2	26	22	33	34	31	42	2	4	6	4
28	23	3	34	21	17	29	44	23	7	49	10	13
29	24	4	35	5	14	4	10	30	44	12	29	42
30	25	1	27	50	43	28	1	38	33	5	12	40
31	26	2	20	19	32	38	43	40	17	24	36	45
32	27	3	45	42	4	4	32	18	19	29	33	14
33	28	4	43	45	24	38	7	44	45	42	17	20
34	29	1	45	10	13	50	36	45	34	29	32	41
35	30	2	38	5	23	49	50	22	25	12	23	8
36	31	3	22	36	17	25	12	10	27	39	40	46
37	32	4
38	33	1
39	34	2
40	35	3
41	36	4
42	37	1
43	38	2
44	39	3
45	40	4
Data

Copy here....

Copy Columns.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2
3
4
5	S.N	Group	n1	n3	n5	n8
6	1	1	n1	n3	n5	n8
7	2	2	n1	n3	n5	n8
8	3	3	n1	n3	n5	n8
9	4	4	n1	n3	n5	n8
10	5	1	27	32	38	11
11	6	2	4	38	26	7
12	7	3	44	35	17	38
13	8	4	46	27	38	11
14	9	1	36	34	38	31
15	10	2	34	7	10	40
16	11	3	43	50	38	1
17	12	4	26	8	2	14
18	13	1	3	26	18	31
19	14	2	38	26	9	15
20	15	3	9	28	3	45
21	16	4	32	11	1	49
22	17	1	13	11	39	24
23	18	2	2	34	26	44
24	19	3	23	9	30	19
25	20	4	22	38	27	20
26	21	1	16	46	21	40
27	22	2	5	48	50	42
28	23	3	16	36	20	44
29	24	4	24	13	48	2
30	25	1	19	4	16	13
31	26	2	26	33	31	4
32	27	3	34	17	44	49
33	28	4	35	14	10	12
34	29	1	27	43	1	5
35	30	2	20	32	43	24
36	31	3	45	4	32	29
37	32	4	43	24	7	42
38	33	1	45	13	36	29
39	34	2	38	23	50	12
40	35	3	22	17	12	39
41	36	4
42	37	1
43	38	2
44	39	3
45	40	4
copy columns

Kind regards,
Moti

 

[HighAndWilder]

Hello HighAndWilder, here is the new data sheet layout and copy results in the sheet copy columns (you will see it is coping data from row1) please check and also if it is not much please can it is possible to paste values only. Thank you

Data sheet....

Copy Columns.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	S.N	Group	n1	n2	n3	n4	n5	n6	n7	n8	n9	n10
2	S.N	Group	n1	n2	n3	n4	n5	n6	n7	n8	n9	n10
3	S.N	Group	n1	n2	n3	n4	n5	n6	n7	n8	n9	n10
4	S.N	Group	n1	n2	n3	n4	n5	n6	n7	n8	n9	n10
5	S.N	Group	n1	n2	n3	n4	n5	n6	n7	n8	n9	n10
6	1	1	27	30	32	29	38	26	36	11	13	44
7	2	2	4	42	38	29	26	43	31	7	47	39
8	3	3	44	9	35	6	17	33	34	38	29	33
9	4	4	46	48	27	28	38	47	28	11	29	44
10	5	1	36	9	34	36	38	16	12	31	42	4
11	6	2	34	47	7	5	10	11	27	40	10	22
12	7	3	43	40	50	30	38	2	46	1	4	28
13	8	4	26	31	8	44	2	26	5	14	17	31
14	9	1	3	12	26	8	18	13	2	31	37	46
15	10	2	38	23	26	1	9	48	3	15	30	14
16	11	3	9	44	28	16	3	33	3	45	43	6
17	12	4	32	41	11	31	1	47	3	49	39	33
18	13	1	13	35	11	17	39	30	18	24	12	27
19	14	2	2	46	34	26	26	45	19	44	50	14
20	15	3	23	30	9	49	30	30	7	19	16	44
21	16	4	22	35	38	20	27	24	40	20	47	46
22	17	1	16	50	46	37	21	45	11	40	50	17
23	18	2	5	1	48	25	50	40	23	42	34	29
24	19	3	16	33	36	29	20	46	20	44	33	25
25	20	4	24	23	13	32	48	15	14	2	46	18
26	21	1	19	46	4	9	16	37	4	13	38	44
27	22	2	26	22	33	34	31	42	2	4	6	4
28	23	3	34	21	17	29	44	23	7	49	10	13
29	24	4	35	5	14	4	10	30	44	12	29	42
30	25	1	27	50	43	28	1	38	33	5	12	40
31	26	2	20	19	32	38	43	40	17	24	36	45
32	27	3	45	42	4	4	32	18	19	29	33	14
33	28	4	43	45	24	38	7	44	45	42	17	20
34	29	1	45	10	13	50	36	45	34	29	32	41
35	30	2	38	5	23	49	50	22	25	12	23	8
36	31	3	22	36	17	25	12	10	27	39	40	46
37	32	4
38	33	1
39	34	2
40	35	3
41	36	4
42	37	1
43	38	2
44	39	3
45	40	4
Data

Copy here....

Copy Columns.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2
3
4
5	S.N	Group	n1	n3	n5	n8
6	1	1	n1	n3	n5	n8
7	2	2	n1	n3	n5	n8
8	3	3	n1	n3	n5	n8
9	4	4	n1	n3	n5	n8
10	5	1	27	32	38	11
11	6	2	4	38	26	7
12	7	3	44	35	17	38
13	8	4	46	27	38	11
14	9	1	36	34	38	31
15	10	2	34	7	10	40
16	11	3	43	50	38	1
17	12	4	26	8	2	14
18	13	1	3	26	18	31
19	14	2	38	26	9	15
20	15	3	9	28	3	45
21	16	4	32	11	1	49
22	17	1	13	11	39	24
23	18	2	2	34	26	44
24	19	3	23	9	30	19
25	20	4	22	38	27	20
26	21	1	16	46	21	40
27	22	2	5	48	50	42
28	23	3	16	36	20	44
29	24	4	24	13	48	2
30	25	1	19	4	16	13
31	26	2	26	33	31	4
32	27	3	34	17	44	49
33	28	4	35	14	10	12
34	29	1	27	43	1	5
35	30	2	20	32	43	24
36	31	3	45	4	32	29
37	32	4	43	24	7	42
38	33	1	45	13	36	29
39	34	2	38	23	50	12
40	35	3	22	17	12	39
41	36	4
42	37	1
43	38	2
44	39	3
45	40	4
copy columns

Kind regards,
Moti

Why have you got multiple header rows in the 'data' worksheet?