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!