Announcement

Collapse
No announcement yet.

Paste Into Range But Skip Protected/Locked Cells

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Paste Into Range But Skip Protected/Locked Cells



    Hi!

    I've been trying to paste formula throughout a range of cells in the same column. Trouble is, I protect some sub-total cells and don't want the formula to paste over those sub-total formulas.

    If I protect the sheet and paste the formula throughout the column including the protected cells, I would get an error message.

    How can I paste the new formulas without getting into trouble of the protected cells?

    Ken

  • #2
    Re: Paste Formula In Range Of Cells Including Protected Cells

    Do You?, or Don't You?, want to paste over the protected subtotal formulas?

    The best way to protect a worksheet and allow your macros to run unhindered is to apply the worksheet Protect method in code with the UserInterfaceOnly property set to True.
    Sub All_Macros(Optional control As Variant)

    Comment


    • #3
      Re: Paste Formula In Range Of Cells Including Protected Cells

      Hmm! I thought I made myself clear but apparently I failed somehow.

      I do not want to paste over the protected cells. I want the paste action to skip the protected cells, but as I paste throughout the range, those protected cells gave me error message and disallowed the other cells to be pasted.

      My actual scenario is pasting a vlookup formula in a monthly Profit and Loss Statement column with lots of sub-totals (cells are intentionally protected) within the column.

      So my dilemma is, how can I paste formula over a range of cells that includes protected cells, without either getting into the error message caused by the protected cells and leave all protected cells as is?

      I hope that makes it much clear.

      Comment


      • #4
        Re: Paste Formula In Range Of Cells Including Protected Cells

        In that case, one option would be to paste to a subset range of the selected area that does not include the protected cells.

        Are you pasting a single formula to all the unprotected cells in the range or is there another range "like it" somewhere that you are copying from?
        Sub All_Macros(Optional control As Variant)

        Comment


        • #5
          Re: Paste Formula In Range Of Cells Including Protected Cells

          The formula I'll be using is a vlookup. I'm trying to update monthly actuals into a forecasting worksheet. The original content of those cells are previous forecast/budget numbers.

          If there's any unique identifier between the unprotected and protected cells, it will be that the protected cells are sub-total formulas and the unprotected cells are all numbers.

          I'm not sure if I can selectively select cells based on the above. If someone tell me it can be done and show me, that will be greatly appreciated.

          Thanks for spending time on this!

          Kenneth

          Comment


          • #6
            Re: Paste Formula In Range Of Cells Including Protected Cells

            You can interrogate each cell of the pasting range to determine if it's "Locked" property is True. You don't need to look specifically at different types of formulas. You build a subset range for all the unlocked cells within the given range (I called it SelRange).

            It can be a time consuming process if the range to be analyzed is very large. If you're only working with a few thousand cells or so the range can be analyzed relatively quickly. If you're spanning all 65k rows you might run into a problem.

            The code to create and copy/paste to a subset range of unlocked cells might look like this:

            Code:
            Sub CopyToUnlocked()
            
                Dim cell As Range, MyRange As Range, SelRange As Range
            
                Set MyRange = Range("B3:D40")
            
                For Each cell In MyRange.Cells
                    If Not cell.Locked Then
                        If Not SelRange Is Nothing Then
                            Set SelRange = Union(cell, SelRange)
                        Else
                            Set SelRange = cell
                        End If
                    End If
                Next cell
                
                Range("A1").Copy SelRange
               
            End Sub
            ...now that assumes you're just copying the contents (perhaps a formula) from cell A1 to the SelRange. An interesting twist is that if A1 was not also unlocked, its locked state would travel along during the first copy/paste action and the next time you try to run the macro it would err out, because it would relock all the cells. In which case you could just use pastespecial with formulas or values instead of the all inclusive (implied in this case) paste.
            Sub All_Macros(Optional control As Variant)

            Comment


            • #7
              Re: Paste Formula In Range Of Cells Including Protected Cells

              Thanks! I'll try

              Comment


              • #8
                Re: Paste Formula In Range Of Cells Including Protected Cells

                Paste Formula In Range Of Cells Including Protected Cells
                Hmm! I thought I made myself clear but apparently I failed somehow.
                VERY clear, you want to paste OVER "Protected Cells"

                Comment


                • #9


                  Re: Paste Into Range But Skip Protected/Locked Cells

                  Dear Dave,

                  "Paste Formula In Range Of Cells Including Protected Cells" is a bit taken out of the context. I was saying in doing so I get an error message (which is normal, of course)

                  My problem is, again, that I'm trying to apply a vlookup into a range of cells in one shot, whereas within the range some of them are sub-totals and are protected and hence my action was denied.

                  I'll try what Aaron suggested and see how it works.

                  Ken

                  Comment

                  Working...
                  X