+ Reply to Thread
Results 1 to 9 of 9

Restrict paste type in only 1 sheet

  1. #1
    Registered User
    Join Date
    01-05-2020
    Location
    London
    MS-Off Ver
    2016
    Posts
    43

    Restrict paste type in only 1 sheet

    Hi guys & girls!

    I have a worksheet that people use all the time to paste lots of information into.

    The most time-consuming part is when you click, hold and scroll down to highlight everything that you want to copy, the data is similar to what you'd find in https://xdcc.horriblesubs.info/.
    If you use Crtl-A then copy, it cause problems when you paste because of all the extra stuff that gets in the sheet.

    While wondering if it is possible to make this quicker using Ctrl-A then on excel then Paste Special to Text, i experimented with:

    Please Login or Register  to view this content.
    Then i'd shortcut the macro with Ctrl-v.


    This works, but it causes issues because this effects all the other sheets in the workbook and it also activates on other open workbooks.

    How can i make this work for only 1 sheet called "Dump"?

    Or, is there another code i can use on that specific sheet that would automatically "paste as text" whenever someone does Ctrl-v?

    Thanks!!

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Restrict paste type in only 1 sheet

    After copying, double-click on the cell where you want to paste values.

    To install the code:
    • Right-click on the Dump sheet tab.
    • Select View Code from the pop-up context menu.
    • Paste the code from below in the worksheet's code module.

    Please Login or Register  to view this content.
    Last edited by AlphaFrog; 02-25-2020 at 08:19 PM.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Registered User
    Join Date
    01-05-2020
    Location
    London
    MS-Off Ver
    2016
    Posts
    43

    Re: Restrict paste type in only 1 sheet

    Thanks for the reply AlphaFrog.

    This doesn't work for some reason?

    Edit- I want to keep the information in rows, rather than paste everything inside 1 cell.
    Attached Files Attached Files
    Last edited by Hunterlock; 02-26-2020 at 12:23 AM.

  4. #4
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Restrict paste type in only 1 sheet

    It doesn't paste everything in one cell.

    Can you better describe "doesn't work" in more detail.

    Your example workbook was blank.

    You do have to Copy a range of cells before double clicking another cell to paste values
    Last edited by AlphaFrog; 02-26-2020 at 12:44 AM.

  5. #5
    Registered User
    Join Date
    01-05-2020
    Location
    London
    MS-Off Ver
    2016
    Posts
    43

    Re: Restrict paste type in only 1 sheet

    I am copying data from a website into the dump sheet, I am not copying a range of cells from excel.

    The test file I'd attached has the macro you'd written. After i select all and copy from the website, i followed your instructions in the worksheet, but nothing happens.

    As previously explained, the website I am copying from looks like: https://xdcc.horriblesubs.info/ (click on any of the bots to get the rows of information).

    I hope that makes sense?

    Thanks again!

  6. #6
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Restrict paste type in only 1 sheet

    Try this...

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    01-05-2020
    Location
    London
    MS-Off Ver
    2016
    Posts
    43

    Re: Restrict paste type in only 1 sheet

    AlphaFrog, I think this is great! Thank you.

    To add to this, is it possible to add lines to unprotect the sheet as soon as the macro activates, then protect again once it finished running?

    Thanks again!

  8. #8
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Restrict paste type in only 1 sheet

    You're welcome.

    Change the password to suit

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    01-05-2020
    Location
    London
    MS-Off Ver
    2016
    Posts
    43

    Re: Restrict paste type in only 1 sheet

    This is perfect!

    Thank you very much!!

+ 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. Restrict Copy/Paste
    By dileepan in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-02-2020, 05:15 PM
  2. Restrict Copy/Paste
    By dileepan in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-31-2020, 02:00 PM
  3. Replies: 2
    Last Post: 12-18-2018, 07:06 AM
  4. Replies: 7
    Last Post: 07-04-2014, 05:34 AM
  5. [SOLVED] To restrict copy and Paste in worksheet
    By flakedew in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-22-2012, 06:03 AM
  6. Restrict Cell type
    By benedictexcel in forum Excel General
    Replies: 2
    Last Post: 02-07-2012, 04:02 PM
  7. Restrict copy paste ability
    By wana be xl master in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-22-2006, 03:30 PM

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