Consulting

Results 1 to 5 of 5

Thread: Copy columns

  1. #1
    VBAX Regular
    Joined
    Dec 2007
    Posts
    26
    Location

    Copy columns

    I like to create a macro to copy columns. I have 100 columns of data in Sheet2. Each column has the same range of data. I want to copy 10 of those columns and paste it into one column in Sheet3. The ten columns that I want to copy will be what is in Sheet1 A1:A10. I would to be in letter reference style. Thank you for any help with this.

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,264
    Location
    You don't say where to copy it to, so I guessed

    [vba]

    Public Sub CopyData()
    Dim mpCell As Range
    Dim i As Long

    With Worksheets("Sheet2")

    i = 1
    For Each mpCell In Worksheets("Sheet1").Range("A1:A10")

    If mpCell.Value <> "" Then

    i = i + 1
    .Cells(1, mpCell.Value).EntireColumn.Copy Worksheets("Sheet1").Cells(1, i)
    End If
    Next mpCell
    End With
    End Sub
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Regular
    Joined
    Dec 2007
    Posts
    26
    Location
    Thank you for your quick reply. The code worked great. But is it possible to paste into one Column--like into Sheet1 Column B1.

  4. #4
    VBAX Tutor
    Joined
    Aug 2007
    Posts
    273
    Location
    [VBA]Public Sub CopyData()
    Dim mpCell As Range
    Dim i As Long

    With Worksheets("Sheet2")

    i = 1
    For Each mpCell In Worksheets("Sheet1").Range("A1:A10")

    If mpCell.Value <> "" Then
    .Cells(1, mpCell.Value).Resize(.Cells(1, mpCell.Value).End(xlDown), 1).Copy _
    Worksheets("Sheet1").Cells(i, 2)
    i = i + .Cells(1, mpCell.Value).End(xlDown).Row
    End If
    Next
    End With
    End Sub[/VBA]

  5. #5
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,264
    Location
    [vba]

    Public Sub CopyData()
    Dim mpCell As Range
    Dim i As Long
    Dim mpLast1 As Long
    Dim mpLast2 As Long
    Dim sh As Worksheet

    With Worksheets("Sheet2")

    i = 1
    mpLast2 = 1
    Set sh = Worksheets("Sheet1")
    For Each mpCell In sh.Range("A1:A10")

    If mpCell.Value <> "" Then

    mpLast1 = .Cells(.Rows.Count, mpCell.Value).End(xlUp).Row
    .Cells(1, mpCell.Value).Resize(mpLast1).Copy sh.Cells(mpLast2, "B")
    mpLast2 = sh.Cells(sh.Rows.Count, "B").End(xlUp).Row + 1
    End If
    Next mpCell
    End With
    End Sub
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

Posting Permissions

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