+ Reply to Thread
Results 1 to 10 of 10

Paste only values into sheet

  1. #1
    Registered User
    Join Date
    05-14-2013
    Location
    Melbourne
    MS-Off Ver
    Excel 2010
    Posts
    15

    Paste only values into sheet

    Hello,

    New to VBA and need some help to tweak the below code. I have a spread sheet with a macro which generates a text file from cols/rows. I want to restrict users from pasting formula/format into this sheet and paste only values. Found the below code from a different thread, but it suddenly stopped working for some reason. It used to paste only values, but now it completely prevents me from pasting anything into the sheet.

    Please Login or Register  to view this content.
    Can someone please help me with the above or even a completely new approach to accomplish what I need?

    Cheers,

    VJ

  2. #2
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Paste only values into sheet

    Try this and see if you get an error:
    Please Login or Register  to view this content.
    If you get an error what line is highlighted and what is the error code?
    Thanks,
    Solus


    Please remember the following:

    1. Use [code] code tags [/code]. It keeps posts clean, easy-to-read, and maintains VBA formatting.
    Highlight the code in your post and press the # button in the toolbar.
    2. Show appreciation to those who have helped you by clicking below their posts.
    3. If you are happy with a solution to your problem, mark the thread as [SOLVED] using the tools at the top.

    "Slow is smooth, smooth is fast."

  3. #3
    Registered User
    Join Date
    05-14-2013
    Location
    Melbourne
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Paste only values into sheet

    Hi Solus,

    Many thanks for your reply!

    The code seems to work now, i.e. when I copy cells with format/formula and paste it into this sheet, it only pastes the values.

    However, I have done a lot of validations via VBA and whenever the user enters an invalid data, a message is displayed and the content in that cell is cleared. So for every message box which is displayed, I get an error (please see attached image).

    Capture.JPG

    VJ

  4. #4
    Registered User
    Join Date
    05-14-2013
    Location
    Melbourne
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Paste only values into sheet

    And here's the error code:
    Capture1.JPG

  5. #5
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Paste only values into sheet

    Do the users have different versions of excel?

    The on error resume next statement was suppressing an error and that's why it wasn't functioning.

  6. #6
    Registered User
    Join Date
    05-14-2013
    Location
    Melbourne
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Paste only values into sheet

    Potentially YES! But the spread sheet is still not released to the business and I'm the only one designing/testing it.

    Is there any other way to accomplish this?

  7. #7
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Paste only values into sheet

    Thats a tough one. You could disable all the paste menu/comandbar buttons so that the only way to paste is with a button you place on your sheet that has code that only pastes values. You could even go a step further and assign the macro the cntl+v shortcut which will override the native windows hotkeys.

  8. #8
    Registered User
    Join Date
    05-14-2013
    Location
    Melbourne
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Paste only values into sheet

    Will the same code work if I assign the macro to CTRL+V?

  9. #9
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Paste only values into sheet

    You can assign this to cntl+v
    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    05-14-2013
    Location
    Melbourne
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Paste only values into sheet

    The above idea seems to work fine... But I also want to disable all other ways to paste data into my work sheet except for CTRL+V (macro)... Is there a way to do this?

    I did find some posts over this topic, but couldn't tailor the code to fit my requirement... Can you help?

+ 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. [SOLVED] should copy and paste the intersect values from sheet 1 to sheet 2
    By Apps in forum Excel Programming / VBA / Macros
    Replies: 22
    Last Post: 03-12-2013, 10:06 AM
  2. [SOLVED] how do I paste ONLY values from one sheet to another
    By OliveMarie in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-24-2012, 02:28 AM
  3. Paste Values To Filtered Sheet
    By LB79 in forum Excel General
    Replies: 0
    Last Post: 10-31-2008, 07:47 AM
  4. based on Cell/Column content ,cut one sheet's values and paste it in other sheet?
    By mindpeace in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-25-2006, 08:33 AM
  5. based on Cell/Column content ,cut one sheet's values and paste it in other sheet?
    By mindpeace in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-24-2006, 10:33 AM

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