Help in Debugging to Unprotect Sheet and insert formula

Haree

Board Regular
Joined
Sep 22, 2019
Messages
85
Office Version
  1. 2016
Hi all, I have a small code which will unprotect a sheet and insert a formula, but when i run it it says you have to unprotect it. Could you please have a look and point my mistake so that i could correct myself.
Thanks

Attaching the Code

VBA Code:
    Dim psheet As Worksheet
    For Each psheet In Worksheets
    If psheet.Name <> "Data Sheet" Or psheet.Name <> "Master Sheet" Or psheet.Name <> "Stock Check" Then
    psheet.Unprotect Password:="STOCK"
    Range("E3").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISBLANK(RC[-3]),,IF(RC[-3]=""Sales"",R[-1]C-1,R[-1]C+1))"
    Range("E3").Select
    Selection.AutoFill Destination:=Range("E3:E2000"), Type:=xlFillDefault
    Range("E3:E2000").Select
    Range("H11").Select
    End If
    Next psheet
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,806
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You need to qualify the ranges with the worksheet. There's also no need to select anything there:

Code:
    If psheet.Name <> "Data Sheet" And psheet.Name <> "Master Sheet" And psheet.Name <> "Stock Check" Then
    psheet.Unprotect Password:="STOCK"
    psheet.Range("E3:E2000").FormulaR1C1 = _
        "=IF(ISBLANK(RC[-3]),,IF(RC[-3]=""Sales"",R[-1]C-1,R[-1]C+1))"
    End If

Note also that I changed your Or criteria to And, otherwise you would do this to every sheet.
 
Solution

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,780
Office Version
  1. 365
Platform
  1. Windows
I think Rory's code is missing a "."
Rich (BB code):
psheet.Range("E3:E2000").FormulaR1C1 =
 

Forum statistics

Threads
1,148,020
Messages
5,744,359
Members
423,863
Latest member
teehexcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top