Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 3 of 3

Thread: Macro copy and paste in next blank column

  1. #1
    Join Date
    9th June 2011
    Posts
    1

    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:


    VB:
    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.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    20th February 2006
    Location
    London, UK
    Posts
    2,977

    Re: Macro copy and paste in next blank column

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

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,206

    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:

    VB:
    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

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 3
    Last Post: January 13th, 2012, 19:45
  2. Replies: 2
    Last Post: January 14th, 2011, 09:17
  3. Macro copy and paste in next blank cell
    By simondevane in forum EXCEL HELP
    Replies: 5
    Last Post: November 19th, 2010, 04:53
  4. VBA To Copy Range & Paste To First Blank Column
    By tommyboy444 in forum EXCEL HELP
    Replies: 6
    Last Post: April 7th, 2009, 01:25

Bookmarks

Posting Permissions

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