+ Reply to Thread
Results 1 to 4 of 4

Copy & pasting cells with a drop down in a protected sheet

  1. #1
    Registered User
    Join Date
    10-16-2014
    Location
    Dusseldorf
    MS-Off Ver
    2010
    Posts
    5

    Angry Copy & pasting cells with a drop down in a protected sheet

    Hi all,

    I've tortured uncle google for hours now, but can't seem to get anywhere with this:

    I have a macro that copies and pastes a specific set of rows to the next available blank row. Column B of those rows has a Data Validation drop down referencing to another sheet (=Referencedata!$C$3:$C$44).
    Works fine. But now I want to protect the sheet to prevent users from removing any rows. So I protect the sheet, tick select locked cells, select unlocked cells and Insert Rows only.

    ...and now the drop down is not available anymore after pasting.

    I've tried unlocking all cells in both the affected sheet as well as the sheet with the validation data. To no avail.

    Help, please?

  2. #2
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Copy & pasting cells with a drop down in a protected sheet

    When it pastes is it PastAsValues or just a paste?

    Also, please attach a sample workbook. Make sure there is just enough data to demonstrate your need.

    Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic

  3. #3
    Registered User
    Join Date
    10-16-2014
    Location
    Dusseldorf
    MS-Off Ver
    2010
    Posts
    5

    Re: Copy & pasting cells with a drop down in a protected sheet

    It's paste as Value & Source Formatting

    I attached the workbook. Appreciate your help.
    Attached Files Attached Files

  4. #4
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Copy & pasting cells with a drop down in a protected sheet

    I am not sure what is causing the dropdown to not work, though I suspect the protection prevents the copying of the DataValidation to the new cells. Just unprotect the sheet before copying then protect it afterword, as below. I am also sure you don't need PasteAsValues after PasteAllUsingSourceTheme. The values come with the "All" part.

    Try this code instead and tell me what happens..
    Please Login or Register  to view this content.
    Last edited by Whizbang; 10-17-2014 at 10:29 AM.

+ 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. Simply Copy and pasting data from one sheet to another sheet but certain cells....
    By cory0789 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-24-2014, 09:36 AM
  2. Replies: 5
    Last Post: 07-15-2013, 03:57 AM
  3. Replies: 6
    Last Post: 07-02-2013, 05:23 AM
  4. [SOLVED] In copy/pasting cells to a new sheet cannot automate cells (1, x) based on user input
    By CEDarnell in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-05-2012, 06:08 PM
  5. Locking cells / copy and pasting drop down lists
    By Barking_Mad in forum Excel General
    Replies: 0
    Last Post: 09-18-2009, 10:46 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