Announcement

Collapse
No announcement yet.

Macro copy and paste in next blank column

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Macro copy and paste in next blank column



    Hi everyone,

    I'm hoping that I can get a little help with a macro question. I'm new to Excel and only recorded my first macro tonight, hence I really understand the code at all. My question is:


    I have a list of 115 names (which could vary) in column “A” that I want to sort randomly. To do this I have entered “=RAND()” next to all the names in column “B” and sorting them by clicking on “Custom Sort” and sorting by Column B. This sorts them randomly each time. I was then able to create a macro to do this, so each time I run the macro it randomly sorts the names.

    Adding to the same macro, I would now like to copy “column A” to the next empty column each time I run the macro, hence populating subsequent columns with new randomly sorted names, if possible I would like the macro to run until I hit stop, e.g. populating 50, 100 etc. columns.

    If someone could post me the complete code, I would very much appreciate it.

    The code I have so far is as follows:


    Code:
    Sub RandomSort()
    '
    ' RandomSort Macro
    ' Randomly Sort column A
    '
    ' Keyboard Shortcut: Option+Cmd+s
    '
        Columns("A:B").Select
        Application.CutCopyMode = False
        ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("B1:B115") _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("Sheet1").Sort
            .SetRange Range("A1:B115")
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End Sub
    Thanks in advance for your help.

  • #2
    Re: Macro copy and paste in next blank column

    If your data are always A1:A115, try adding this line at the start:
    Code:
    Range("A1:A115").Copy Cells(1, Columns.Count).End(xlToLeft).Offset(, 1)

    Comment


    • #3


      Re: Macro copy and paste in next blank column

      if possible I would like the macro to run until I hit stop, e.g. populating 50, 100 etc. columns.
      I suggest using an input box to prompt for the number of iterations.

      Adapt this as needed - basic example:

      Code:
      Dim i As Long
      
      i = Application.InputBox("Enter number of iterations", Type:=1)
      
      If Not i > 0 Then Exit Sub
      
      For i = 1 To i
          Range("A1:A115").Copy Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1)
      Next i
      AAE
      ----------------------------------------------------

      Forum Rules | Message to Cross Posters | How to use Tags

      Comment

      Working...
      X