Macro - Skip Sheet when Sheet is missing

[Mickeyc]

Hi, relatively new at using macros, also first post, thanks in advance for any help, current code below

So for example if "Piping" sheet is missing, I am receiving an Error message, but I want the macro to skip to the next sheet, so that the missing sheet is ignored and also the commands in that sheet are ignored,

If any more details are needed please let me know?

Thanks

Mick

Sub Macro3()
'
' Macro3 Macro
'


'
Sheets("Process").Select
ActiveSheet.Unprotect
Sheets("Piping").Select
ActiveSheet.Unprotect
Sheets("Mechanical").Select
ActiveSheet.Unprotect
Sheets("Electrical").Select
ActiveSheet.Unprotect
Sheets("Instrumentation").Select
ActiveSheet.Unprotect
Sheets("Safety").Select
ActiveSheet.Unprotect
Sheets("Civil").Select
ActiveSheet.Unprotect
Sheets("Architecture").Select
ActiveSheet.Unprotect
Sheets("HVAC").Select
ActiveSheet.Unprotect
Sheets("Structural").Select
ActiveSheet.Unprotect
Sheets("Pipeline").Select
ActiveSheet.Unprotect
Sheets("1.Engineering Cost").Select
End Sub

 

[Mickeyc]

Sorry forgot to mention Excel 2013

 

[Michael M]

What about this to Unprotect

Sub MM1()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Unprotect
Next ws
End Sub

and this to Protect

Sub MM1()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Protect
Next ws
End Sub

 

[Mickeyc]

Hi Michael M,

Thanks very much for the quick reply, I am ok with the unprotect part, that is irrelevant to my question, apologies I probably did not give enough details,

So, I have the macro set up to do the following, I click run "Macro 1" and the following happens

Process sheet opens
Process sheet is unlocked

Piping sheet opens
Piping sheet is unlocked

Mechanical sheet opens
Mechanical sheet is unlocked

Electrical sheet opens
Electrical sheet is unlocked

Instrumentation sheet opens
Instrumentation sheet is unlocked

Safety sheet opens
Safety sheet is unlocked

Civil sheet opens
Civil sheet is unlocked

Architecture sheet opens
Architecture sheet is unlocked

HVAC sheet opens
HVAC sheet is unlocked

Structural sheet opens
Structural sheet is unlocked

Pipeline sheet opens
Pipeline sheet is unlocked

1.Engineering Cost sheet opens

"Macro 1" is then finished

Here is the problem, if one of the sheets above is missing a Runtime error occurs

I need the sheets that are missing to be skipped/ignored, so for example if the sheet Mechanical and the sheet Structure are missing, I need the follow to happen

Process sheet opens
Process sheet is unlocked

Piping sheet opens
Piping sheet is unlocked

<strike>Mechanical sheet opens</strike>
<strike>Mechanical sheet is unlocked</strike>

Electrical sheet opens
Electrical sheet is unlocked

Instrumentation sheet opens
Instrumentation sheet is unlocked

Safety sheet opens
Safety sheet is unlocked

Civil sheet opens
Civil sheet is unlocked

Architecture sheet opens
Architecture sheet is unlocked

HVAC sheet opens
HVAC sheet is unlocked

<strike>Structural sheet opens</strike>
<strike>Structural sheet is unlocked</strike>

Pipeline sheet opens
Pipeline sheet is unlocked

1.Engineering Cost sheet opens

Please let me know if any other informations is needed

Thanks

Mick

 

[Michael M]

If the Requirement is that ALL sheets need to be unprotected, my code will do that.
Did you try it ??

 

[Mickeyc]

Hi Michael M,

Thanks again for the quick reply, the unprotection is just a coincidence, for example I have the same problem with the following code

Sub viewNorms()
'
' viewNorms Macro
'


'
Sheets("Process").Select
Range("U4:W5").Select
ActiveCell.FormulaR1C1 = "View Norms"
Application.Goto Reference:="R1C3"
Sheets("Piping").Select
Range("U4:W5").Select
ActiveCell.FormulaR1C1 = "View Norms"
Application.Goto Reference:="R1C3"
Sheets("Mechanical").Select
Range("U4:W5").Select
ActiveCell.FormulaR1C1 = "View Norms"
Application.Goto Reference:="R1C3"
Sheets("Electrical").Select
Range("U4:W5").Select
ActiveCell.FormulaR1C1 = "View Norms"
Application.Goto Reference:="R1C3"
Sheets("Instrumentation").Select
Range("U4:W5").Select
ActiveCell.FormulaR1C1 = "View Norms"
Application.Goto Reference:="R1C3"
Sheets("Safety").Select
Range("U4:W5").Select
ActiveCell.FormulaR1C1 = "View Norms"
Application.Goto Reference:="R1C3"
Sheets("Civil").Select
Range("U4:W5").Select
ActiveCell.FormulaR1C1 = "View Norms"
Application.Goto Reference:="R1C3"
Sheets("Architecture").Select
Range("U4:W5").Select
ActiveCell.FormulaR1C1 = "View Norms"
Application.Goto Reference:="R1C3"
Sheets("HVAC").Select
Range("U4:W5").Select
ActiveCell.FormulaR1C1 = "View Norms"
Application.Goto Reference:="R1C3"
Sheets("Structural").Select
Range("U4:W5").Select
ActiveCell.FormulaR1C1 = "View Norms"
Application.Goto Reference:="R1C3"
Sheets("Pipeline").Select
Range("U4:W5").Select
ActiveCell.FormulaR1C1 = "View Norms"
Application.Goto Reference:="R1C3"
Sheets("1.Engineering Cost").Select
Application.Goto Reference:="R1C1"
End Sub

When a sheet names above is missing the same Runtime error occurs, I need a code that will skip a sheet that is missing,

The sheets mentioned above are always present at the start, then some get deleted as they are not required, I need the code to skip these deleted sheets

Thanks for your efforts

Mick

 

[Michael M]

Use the same method as I posted earlier

Sub MM1()
Dim ws As Worksheet
For Each ws In Worksheets
Range("U4:W5").value = "View Norms"
Next ws
End Sub

 

[mikerickson]

To test if a sheet exists, you could use a function like

Function SheetExists(SheetName as String) As Boolean
    On Error Resume Next
    SheetExists = (LCase(ThisWorkbook.Sheets(SheetName).Name) = LCase(SheetName))
    On Error Goto 0
End Function

Then you code could read like

If SheetExits("Piping") Then
    Sheets("Piping").UnProtect
End If

 

[Mickeyc]

Hi Michael M,

Thanks again for your reply, i'm not explaining myself correctly again, there are multiple other sheets that I don't want altered in the file, your code works for all sheets but I only want a code that affects Sheets("Process", "Piping", "Electrical", "Mechanical", "Instrumentation", "Safety", "Civil", "Architecture", "Structural" and "Pipeline")


Thanks for your efforts


Hi Mikerickson,

Thanks for your reply,

I entered the below code as a Macro to test if it would work for the piping sheet only but I am getting the following error message

"Compile Error"

"Expected End Sub"

Mick

Sub test()



Function SheetExists(SheetName As String) As Boolean
On Error Resume Next
SheetExists = (LCase(ThisWorkbook.Sheets(SheetName).Name) = LCase(SheetName))
On Error GoTo 0
End Function


If SheetExists("Piping") Then
Sheets("Piping").Unprotect
End If
End Function


End Sub

 

[mikerickson]

The coding should reflect two un-nested procedures

Sub test()
    If SheetExists("Piping") Then
        Sheets("Piping").Unprotect
    End If
End Sub

Function SheetExists(SheetName As String) As Boolean
    On Error Resume Next
    SheetExists = (LCase(ThisWorkbook.Sheets(SheetName).Name) = LCase(SheetName))
    On Error GoTo 0
End Function