Copy paste only selected columns to another locations

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
2,353
Office Version
  1. 2010
Hello,</SPAN></SPAN>

I got lottery results in the cells C6:J1000, I want to copy few columns as per my prior selection, for example I want to be coped only those column which I selected marking "X" in the cells C4:J4 in the example there are 5 selected out of 8 which I need to be copied and pasted from the column "M" to next columns all data as long as find in columns C6:J6 to end rows
</SPAN></SPAN>


Book1
ABCDEFGHIJKLMNOPQ
1
2
3
4XXXXX
5Numbersn1n2n3n4n5*1*2Numbersn2n3*1*2
613610639316101106610
72353326405382332638
8121030441181103018
94433819104611143819111
105230384346725303872
1182710304712988103098
121015402448382910402429
1312293223151712322317
14136211069621211062
157463227341487322748
1679613283745761345
177314928203527492852
18532250103761052250610
195321439303951439
20542232537111523111
219135331591195911
228372813411108281110
23594016141554015
244923817142371
25326121406316
2627544342252
272142119241122111
2821512264282128
2924312421952125
3024193611121911
Sheet3


Thank you all
</SPAN></SPAN>

Excel 2000
</SPAN></SPAN>
Regards,
</SPAN>
Moti
</SPAN>
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Try this:-
Code:
[COLOR="Navy"]Sub[/COLOR] MG19Jul52
[COLOR="Navy"]Dim[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant, nRay [COLOR="Navy"]As[/COLOR] Variant, Rng [COLOR="Navy"]As[/COLOR] Range, R [COLOR="Navy"]As[/COLOR] Range, Num [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Ray = Range("C5:J30")
[COLOR="Navy"]Set[/COLOR] Rng = Range("C4:J4")
[COLOR="Navy"]For[/COLOR] n = 1 To Rng.Count
    [COLOR="Navy"]If[/COLOR] Not IsEmpty(Rng(n).Value) [COLOR="Navy"]Then[/COLOR]
        Num = Num & IIf(Num = "", n, "," & n)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] n

nRay = Application.Index(Ray, Evaluate("Row(1:" & UBound(Ray, 1) & ")"), Split(Num, ","))
Range("M5").Resize(UBound(Ray, 1), UBound(Split(Num, ",")) + 1) = nRay
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Try this:-
Code:
[COLOR=navy]Sub[/COLOR] MG19Jul52
[COLOR=navy]Dim[/COLOR] Ray [COLOR=navy]As[/COLOR] Variant, nRay [COLOR=navy]As[/COLOR] Variant, Rng [COLOR=navy]As[/COLOR] Range, R [COLOR=navy]As[/COLOR] Range, Num [COLOR=navy]As[/COLOR] [COLOR=navy]String,[/COLOR] n [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
Ray = Range("C5:J30")
[COLOR=navy]Set[/COLOR] Rng = Range("C4:J4")
[COLOR=navy]For[/COLOR] n = 1 To Rng.Count
    [COLOR=navy]If[/COLOR] Not IsEmpty(Rng(n).Value) [COLOR=navy]Then[/COLOR]
        Num = Num & IIf(Num = "", n, "," & n)
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] n

nRay = Application.Index(Ray, Evaluate("Row(1:" & UBound(Ray, 1) & ")"), Split(Num, ","))
Range("M5").Resize(UBound(Ray, 1), UBound(Split(Num, ",")) + 1) = nRay
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Regards Mick
Mick, macro result excellent it copies header too, which I did, not thought before it is solved 100% ok.</SPAN></SPAN>

I appreciate your kind help
</SPAN></SPAN>

Have a good day
</SPAN></SPAN>

Kind Regards,
</SPAN>
Moti :)
</SPAN></SPAN>
 
Upvote 0
You're welcome
Another option gives you your formatting if required !!!
Code:
[COLOR="Navy"]Sub[/COLOR] MG19Jul24
[COLOR="Navy"]Dim[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant, nRay [COLOR="Navy"]As[/COLOR] Variant, Rng [COLOR="Navy"]As[/COLOR] Range, R [COLOR="Navy"]As[/COLOR] Range, Num [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Ac = 9
[COLOR="Navy"]Set[/COLOR] Rng = Range("C4:J4")
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] R [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not IsEmpty(R) [COLOR="Navy"]Then[/COLOR]
        Ac = Ac + 1
        R.Offset(1).Resize(30).Copy Rng(1).Offset(1, Ac)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] R
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Solution
You're welcome
Another option gives you your formatting if required !!!
Code:
[COLOR=navy]Sub[/COLOR] MG19Jul24
[COLOR=navy]Dim[/COLOR] Ray [COLOR=navy]As[/COLOR] Variant, nRay [COLOR=navy]As[/COLOR] Variant, Rng [COLOR=navy]As[/COLOR] Range, R [COLOR=navy]As[/COLOR] Range, Num [COLOR=navy]As[/COLOR] [COLOR=navy]String,[/COLOR] Ac [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
Ac = 9
[COLOR=navy]Set[/COLOR] Rng = Range("C4:J4")
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] R [COLOR=navy]In[/COLOR] Rng
    [COLOR=navy]If[/COLOR] Not IsEmpty(R) [COLOR=navy]Then[/COLOR]
        Ac = Ac + 1
        R.Offset(1).Resize(30).Copy Rng(1).Offset(1, Ac)
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] R
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Regards Mick
Mick, this option is too good is coping and pasting every column and header fonts, background colours of cell fills as it is. Wow!</SPAN></SPAN>

Thank you so much for this alteration. :)
</SPAN></SPAN>

Kind Regards,
</SPAN></SPAN>
Moti
</SPAN></SPAN>
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,938
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top