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

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

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

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" <[email protected]> wrote in message
news:OftIL%[email protected]...
> 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
> Rgds
> Max
> xl 97
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com

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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Jim Cone" <[email protected]> wrote in message
news:[email protected]...
> 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,

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" <[email protected]> wrote in message
news:[email protected]...
> 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
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com

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

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Jim Cone" <[email protected]> wrote in message
news:[email protected]...
> 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