+ Reply to Thread
Results 1 to 9 of 9

Macro to copy and paste from list to same cell

Hybrid View

  1. #1
    Registered User
    Join Date
    01-14-2011
    Location
    aanekoski
    MS-Off Ver
    Excel 2003
    Posts
    5

    Macro to copy and paste from list to same cell

    I want to make macro that copies values in a1 to a100 to cell c10 everytime I click enter.(first enter a1 to c10,second enter a2 to c10 and so on to the end of the list).

    Thank you in advance

  2. #2
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Macro to copy and paste from list to same cell

    Maybe:

    Sub dogholiday()
    Dim x As String
    Dim i As Long
    Range("C10").ClearContents
    For i = 1 To Range("A" & Rows.count).End(3)(1).Row
        x = x & Range("A" & i).Value & ", "
        x = x
    Next i
    Range("C10").Value = x
    Range("C10").Value = Left(Range("C10"), Len(Range("C10")) - 2)
    Range("C10").Value = Right(Range("C10"), Len(Range("C10")) - 2)
    End Sub
    Last edited by JOHN H. DAVIS; 05-20-2014 at 03:44 PM.

  3. #3
    Registered User
    Join Date
    01-14-2011
    Location
    aanekoski
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Macro to copy and paste from list to same cell

    Thank you very much,but that code didn't clear c10 cell list did come like this to c10 : 4, 6, 4, 3, 2, 1, 3, 5
    They should come one by one after enter: 4 enter => 4 to c10, second enter 6 to c10 and so on.

  4. #4
    Registered User
    Join Date
    01-14-2011
    Location
    aanekoski
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Macro to copy and paste from list to same cell

    Any Idea yet?

  5. #5
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Macro to copy and paste from list to same cell

    Can you provide a sample workbook with before and after? On my test it seemed to do what you requested.

  6. #6
    Registered User
    Join Date
    01-14-2011
    Location
    aanekoski
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Macro to copy and paste from list to same cell

    Here I try to demonstrate what I want to do. I have dog id numbers in column A and when i push enter button the first id goes to cell F1 and that dog's pedigree will be made.After second enter the second id goes to F1 and new pedigree will be made and so on
    pedigree.JPG

  7. #7
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Macro to copy and paste from list to same cell

    OK I think I misunderstood. One way is too create a Sheet called "Helper" then number the cells in Column A 1 to 100 reflecting the row number.

    Then you can use this macro.

    Sub dogholidayzz()
    
    Range("C10").ClearContents
    Range("A" & Sheets("Helper").Range("A1").Value).Copy Range("C10")
    Sheets("Helper").Range("A1").Delete
        
    End Sub
    Probably more elegant ways though.

  8. #8
    Registered User
    Join Date
    01-14-2011
    Location
    aanekoski
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Macro to copy and paste from list to same cell

    Thank you worked fine with values but not if text.

  9. #9
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Macro to copy and paste from list to same cell

    Not sure what you mean? Can you attach a file with some sample data where it doesn't work. To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. macro copy formula then paste data the length of a list
    By BobbyH in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-09-2013, 08:29 PM
  2. Replies: 4
    Last Post: 02-14-2013, 02:12 PM
  3. Help making a copy paste Macro for data from drop down list
    By sleep in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-08-2012, 05:21 AM
  4. Excel Macro help to copy/paste results using input values from a list box
    By kamila7 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-13-2012, 04:46 PM
  5. Copy and Paste macro needs to paste to a changing cell reference
    By loulou in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-24-2005, 07:06 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1