Exclude Sheet from Formula

[Acans]

Hi all,

I'm an excel noob and I've been using this script I found online for a few years now to remove Formula's by just doing a mass copy/paste as value.

I was wondering if there's an IF statement or something I can add to it to exclude a sheet IF name equals true.

Sub Formula_Zapper()
 Worksheets.Select
 Cells.Select
 Selection.Copy
 Selection.PasteSpecial Paste:=xlPasteValues
 ActiveSheet.Select
 Application.CutCopyMode = False
End Sub

If there is a better way of doing the above I'm also open to suggestions

[JKwan]

Sub test()
    If ActiveSheet.Name <> "Sheet1" Then
        MsgBox "Sheet1"
    Else
        MsgBox "Other sheet"
    End If
End Sub

[Paul_Hossler]

I'd do something like this

Option Explicit


Sub Formula_Zapper()
    Dim ws As Worksheet
    
    
    With ActiveWorkbook
        If .ProtectWindows Or .ProtectStructure Then
            MsgBox "This workbook is password protected"
            Exit Sub
        End If
    End With
    
    Application.ScreenUpdating = False
    
    For Each ws In ActiveWorkbook.Worksheets
        With ws
            If UCase(.Name) = "NOTTHISONE" Then GoTo NextWS
            If .ProtectContents Then GoTo NextWS
            
            .UsedRange.Value = .UsedRange.Value
        End With
NextWS:
    Next


    Application.ScreenUpdating = True
End Sub

[Acans]

Hi Paul,

I tried what you put above, however it gave a 1004 error due a sheet containing a Pivot Table. The sheet I want to exclude contains a Pivot Table, and another sheet I don't want to exlcude from the formular contains a Pivot table.

Would I also need to exclude the second sheet that contains Pivot Tables?

[Paul_Hossler]

Not sure I followed that, but to not delete a sheet with a PT on it, just add the marked line

Option Explicit


Sub Formula_Zapper()
    Dim ws As Worksheet
    
    
    With ActiveWorkbook
        If .ProtectWindows Or .ProtectStructure Then
            MsgBox "This workbook is password protected"
            Exit Sub
        End If
    End With
    
    Application.ScreenUpdating = False
    
    For Each ws In ActiveWorkbook.Worksheets
        With ws
            If UCase(.Name) = "SHEET1" Then GoTo NextWS
            If .ProtectContents Then GoTo NextWS
            If .PivotTables.Count > 0 Then GoTo NextWS  '   <<<<<<<<<<<<<
            
            .UsedRange.Value = .UsedRange.Value
        End With
NextWS:
    Next




    Application.ScreenUpdating = True
End Sub

[Acans]

Hi Paul,

Perfect that did the trick. Thanks for your help!