Copy paste only selected columns to another locations
Results 1 to 6 of 6

Thread: Copy paste only selected columns to another locations

  1. #1
    Board Regular
    Join Date
    Feb 2008
    Posts
    1,733
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Copy paste only selected columns to another locations

    Hello,

    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


    #E0E0F0 " />#E0E0F0 ;text-align: center;color: #161120">
    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

    #E0E0F0 ;color: #161120">Sheet3





    Thank you all


    Excel 2000

    Regards,

    Moti


  2. #2
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,820
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Copy paste only selected columns to another locations

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

  3. #3
    Board Regular
    Join Date
    Feb 2008
    Posts
    1,733
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy paste only selected columns to another locations

    Quote Originally Posted by MickG View Post
    Try this:-
    Code:
    Sub MG19Jul52
    Dim Ray As Variant, nRay As Variant, Rng As Range, R As Range, Num As String, n As Long
    Ray = Range("C5:J30")
    Set Rng = Range("C4:J4")
    For n = 1 To Rng.Count
        If Not IsEmpty(Rng(n).Value) Then
            Num = Num & IIf(Num = "", n, "," & n)
        End If
    Next n
    
    nRay = Application.Index(Ray, Evaluate("Row(1:" & UBound(Ray, 1) & ")"), Split(Num, ","))
    Range("M5").Resize(UBound(Ray, 1), UBound(Split(Num, ",")) + 1) = nRay
    End Sub
    Regards Mick
    Mick, macro result excellent it copies header too, which I did, not thought before it is solved 100% ok.

    I appreciate your kind help


    Have a good day


    Kind Regards,

    Moti


  4. #4
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,820
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Copy paste only selected columns to another locations

    You're welcome
    Another option gives you your formatting if required !!!
    Code:
    Sub MG19Jul24
    Dim Ray As Variant, nRay As Variant, Rng As Range, R As Range, Num As String, Ac As Long
    Ac = 9
    Set Rng = Range("C4:J4")
    For Each R In Rng
        If Not IsEmpty(R) Then
            Ac = Ac + 1
            R.Offset(1).Resize(30).Copy Rng(1).Offset(1, Ac)
        End If
    Next R
    End Sub
    Regards Mick

  5. #5
    Board Regular
    Join Date
    Feb 2008
    Posts
    1,733
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy paste only selected columns to another locations

    Quote Originally Posted by MickG View Post
    You're welcome
    Another option gives you your formatting if required !!!
    Code:
    Sub MG19Jul24
    Dim Ray As Variant, nRay As Variant, Rng As Range, R As Range, Num As String, Ac As Long
    Ac = 9
    Set Rng = Range("C4:J4")
    For Each R In Rng
        If Not IsEmpty(R) Then
            Ac = Ac + 1
            R.Offset(1).Resize(30).Copy Rng(1).Offset(1, Ac)
        End If
    Next R
    End Sub
    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!

    Thank you so much for this alteration.


    Kind Regards,

    Moti


  6. #6
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,820
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Copy paste only selected columns to another locations

    You're welcome

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •