+ Reply to Thread
Results 1 to 9 of 9

Excel Button to copy selection and then paste special values over it

  1. #1
    Registered User
    Join Date
    04-19-2010
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    23

    Excel Button to copy selection and then paste special values over it

    Hello

    This is probably relatively easy but I can't find the relevant instructions on the net, can't even get past assigning macros to buttons as there is no 'assign macro' option when I right click on the command button (editing enabled).

    Anyway... I am looking to make a button that when pressed will select the data in a range of cells, copy it, and then paste special > values that data into the same range.

    If I can, I'd like help making one button which, when pressed, selects the data to the right of the highlighted cell. If this is too difficult, a button for each row would suffice.

    If anyone can point me in the right direction it would be much appreciated. The most I've worked out is a macro that, when run, copies and pastes the data in a specific range... but this isn't based on what cell is selected.

    The purpose of this is to make data entry easier, I'm creating a data entry sheet for traffic surveys and having to copy a range of cells, then paste special values it back into the same range might be beyond some of those who will use the data entry sheet, and so a button to do this would make life easier.

    Attached are the 2 data entry sheets we currently have finished.

    Thanks in advance for any pointers, I'll keep googling in the mean time and trying to learn the basics of VBA.
    Attached Files Attached Files
    Last edited by transportplanner; 04-14-2011 at 07:00 AM.

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Excel Button to copy selection and then paste special values over it

    Hi,

    It wasn't quite clear precisely what you wanted to copy and exactly where to paste it. However I think what you may be looking for is as follows. It will copy the whole of the row which contains the active cell, i.e. (where the cursor was last clicked), and paste back as values.

    Please Login or Register  to view this content.
    HTH
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    04-19-2010
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    23

    Re: Excel Button to copy selection and then paste special values over it

    That's brilliant thanks very much

    Any idea how to make it select just the data values in the range to the right of the data value selected, rather than the entire row?

    Am googling for activecell instructions but have only found things like (xlToLeft) which doesn't do what I want

    Thanks

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Excel Button to copy selection and then paste special values over it

    Hi,

    If there is a contiguous range of cells to the right of the active cell that you're copying then
    Please Login or Register  to view this content.
    if there are gaps in the cells to the right then

    Please Login or Register  to view this content.
    Regards
    Last edited by Richard Buttrey; 04-08-2011 at 11:28 AM.

  5. #5
    Registered User
    Join Date
    04-19-2010
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    23

    Re: Excel Button to copy selection and then paste special values over it

    brilliant, will test this out on monday.

    may i ask what the"A" & bit of the code is? doesn't say anything about that, read a bit of Excel VBA for dummies and looked on the net a bit :S

  6. #6
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Excel Button to copy selection and then paste special values over it

    Hi,

    It's simply there since you need to perform an End Left from the furthest right hand column. i.e. from IV if using Excel 2003 or XFD if using 2007/2010.

    Hence the offset number (which is the number of column less 1) needs to start in column A (to ensure offset puts us in either IV or XFD) and the end left starts with the furthest column to the right.

    HTH

  7. #7
    Registered User
    Join Date
    04-19-2010
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    23

    Re: Excel Button to copy selection and then paste special values over it

    Thanks for all the guidance.

    I'm sorry if this is a stupid mistake I'm making, but I cannot get the code to work.

    I copied and pasted your code in, in exactly the same way I did with the earlier simpler code.

    The earlier simpler code looks like this:

    Private Sub CommandButton6_Click()
    ActiveCell.EntireRow.Copy
    Range("A" & ActiveCell.Row).PasteSpecial (xlPasteValues)
    End Sub

    And that works, although it effects cells outside of the range I wish to be effected.

    So, I've tried your newer code and haven't succeeded with either one,

    In both cases, whether I have 1 End Sub or 2, and with or without the line that links it to the commandbutton6, having the code makes it not work. I think the issue is the word Sub at the beginning of the first line, making it have issue with Private Sub

    The error code I get is: Compile Error: Expected End Sub
    It highlights the first line Private Sub CommandButton6_Click() in yellow

    Pretty much all of this is completely beyond me, so thanks very much for any help you can give. If you get bored with me the first solution is workable just trying to learn these things.

  8. #8
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Excel Button to copy selection and then paste special values over it

    Hi,

    in your
    Please Login or Register  to view this content.
    procedure just call the procedure I gave you with a single line of code. i.e.

    Please Login or Register  to view this content.
    or just attach the CopyToContiguousRange macro to a button.

    Regards

    Regards

  9. #9
    Registered User
    Join Date
    04-19-2010
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    23

    re: Excel Button to copy selection and then paste special values over it

    Thanks for the help

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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