Copy & Paste Cells Excluding Formatting & Protection

  • I have the following code from a form. It works just fine copying named ranges from one sheet to another. The probloem now is that I don't want to copy the characteristic of the formating or whether they are protected or not. The originating spreadsheets have cells that are protected. I am trying to copy those values over to the new sheet and paste their values or formulas but not their formatting (background color) or the fact that some of the cells are 'locked'. I want the new sheet to have the values and formulas but not the be lock for the user. Is this possible with the current way I do the code or do I have to rewrite it? How would you approach this?


    Thanks,


    Guy
    PS: Happy Holidays to All! :catinthe:


  • Re: Copy And Paste Excluding Formatting And 'protection'


    Code
    Range("MyRange").Copy
        With Sheet2.Range("A1")
               .PasteSpecial XlValues
               .PasteSpecial XlFormulas
         End With
    Application.CutCopyMode=False
  • Re: Copy And Paste Excluding Formatting And 'protection'


    Quote from Dave Hawley
    Code
    Range("MyRange").Copy
        With Sheet2.Range("A1")
               .PasteSpecial XlValues
               .PasteSpecial XlFormulas
         End With
    Application.CutCopyMode=False


    Dave, thanks! IF I wanted to also include the protection characteristic what would I use?


    Is there a place on your site that lists the PasteSpecial options?


    Regards,


    Dan

  • Re: Copy & Paste Cells Excluding Formatting & Protection


    Quote

    Is there a place on your site that lists the PasteSpecial options?


    Open the VBE, press the F1 key and type Pastespecial into the seach field. Expand the help for "PasteSpecial Method as it applies to a Range object", then click the link for xlPasteType to see the options.

  • Re: Copy & Paste Cells Excluding Formatting & Protection


    Quote


    Dave, thanks! IF I wanted to also include the protection characteristic what would I use?

    Nothing, Cells are Locked as their default. If the destination range has been unlocked, use code like;

    Code
    Range("MyRange").Copy
    With Sheet2.Range(Sheet2.Cells(1, 1), _
        Sheet2.Cells(Range("MyRange").Rows.Count, Range("MyRange").Columns.Count))
        .PasteSpecial xlValues
        .PasteSpecial xlFormulas
        .Locked = True
    End With
    Application.CutCopyMode = False

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!