Problem with Paste method

**royUK** · Sep 20th 2020

What code have you got in the WorkBook open event?

JimmyB · Sep 20th 2020

Good Morning...well it's still morning here anyway...LOL

Here's the code I used. The line is imbedded at the end of the 'for' statement .

Heading out for the day. Will check back later.

Thanks JimmyB

It normally works on all of the other 43 sheets

Code

 '''This next block shuts off gridlines & headings on the sheets _
            with the exceptions as listed in the "If" statement
            
        '''This also allows the macros to run on protected sheets

    For Each ws In ThisWorkbook.Worksheets
        If ws.name = "VALUES for MEALS" Or ws.name = "Calories" Or ws.name = "Tot Fat" _
        Or ws.name = "Trans Fat" Or ws.name = "Cholesterol" Or ws.name = "Sodium" _
        Or ws.name = "Carbs" Or ws.name = "Fibre" Or ws.name = "Sugar" _
        Or ws.name = "Protein" Or ws.name = "Meals Data" _
        Or ws.name = "Sheet1" Or ws.name = "Sheet2" Or ws.name = "Sheet3" Or ws.name = "Sheet4" _
        Or ws.name = "Sheet5" Or ws.name = "Sheet6" Then GoTo Jump1
        ws.Activate
        ActiveWindow.DisplayGridlines = False
        ActiveWindow.DisplayHeadings = False
Jump1:
        ws.Protect "Pamela491", UserInterfaceOnly:=True, DrawingObjects:=True, Contents:=True, Scenarios:=True
    Next ws

Display More

**royUK** · Sep 21st 2020

This line should allow macros to work on the sheets in the If statement

Code

ws.Protect "Pamela491", UserInterfaceOnly:=True, DrawingObjects:=True, Contents:=True, Scenarios:=True

JimmyB · Sep 22nd 2020

Yeah it's weird. It works on all of the 43 sheets just fine...just not that one...No real matter though, I went around it by Unprotecting at the start of the sub and protecting at the end. Works fine

Anyway thanks for your help

JimmyB

Stay safe & have a great week

Problem with Paste method

Participate now!

Share