Kill all formulas on all visible sheets w/o affecting sheet formatting

[Max]

Hi guys,

I'm trying to use the Sub KillAllFormulas() to work on all the visible
sheets in the book, but the loop doesn't work (seems to stop on the active
sheet).

What I'm after is a sub which will simply kill all the formulas on all
visible sheets and retain all sheet formatting as-is (including merged
cells, if any). The latter Sub UnmergeSheet() below was recorded/used as
presence of merged cells seems to halt the "paste special as values"
process.

Any insights appreciated. Thanks.

-----
Sub KillAllFormulas()
Dim s As Worksheet
Application.DisplayAlerts = False
For Each s In ActiveWorkbook.Worksheets
UnmergeSheet
Cells.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Next
Application.DisplayAlerts = True
End Sub

Sub UnmergeSheet()
Cells.Select
With Selection
.VerticalAlignment = xlBottom
.Orientation = 0
.ShrinkToFit = False
.MergeCells = False
End With
End Sub

 

[Jim Cone]

Hi Max,

You need to prefix all of your range and cells statements with the sheet object...
s.Cells.Copy
s.Range("Al")
etc.
'-----------------
Also, you need to pass the sheet object to the UnmergeSheet sub...

UnmergeSheet s

Sub UnmergeSheet(ByRef sht as worksheet)
Sht.Cells.Select...
'-----------------
Also, you could consider using the "SpecialCells" method to find
only the cells with formulas.

Regards,
Jim Cone
San Francisco, USA

 

[Max]

Thanks, Jim.

I've amended the subs below as advised (hope I did it correctly ..). But
stepping thru' Sub KillAllFormulas() with F8 halted the run at this line:

sht.Cells.Select

Error msg:
Run Time error 1004
Select method of Range class failed

Also, you could consider using the "SpecialCells" method to find
only the cells with formulas.

I did try recording via: F5 > Special > Formulas > OK
but it failed to clear even the "Copy" stage as it errored out:
"Command cannot be used on multiple selections"
so couldn't proceed any further with paste special > values ..

---- Amended ------
Sub KillAllFormulas()
Dim s As Worksheet
Application.DisplayAlerts = False
For Each s In ActiveWorkbook.Worksheets
UnmergeSheet s 'Calls this 1st
s.Cells.Copy
s.Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Next s
Application.DisplayAlerts = True
End Sub

Sub UnmergeSheet(ByRef sht As Worksheet)
sht.Cells.Select
With Selection
.VerticalAlignment = xlBottom
.Orientation = 0
.ShrinkToFit = False
.MergeCells = False
End With
End Sub

 

[Jim Cone]

Max,

You can't select cells on a sheet unless it is the active sheet.
The good news is that you don't need to select them.

Change from...
sht.Cells.Select
With Selection

Change to...
With sht.Cells
'-------------

You haven't got to it yet but do the same thing with...
s.Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
Change to...
s.Range("A1").PasteSpecial Paste:=xlValues, Operation:=xlNone,

I have not tested any of this, that's up to you. <g>

Regards,
Jim Cone

 

[Max]

Works ok with the suggested revisions, Jim !
Many thanks for your assistance ..