Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 <atyahoo<dotcom ---- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Kill all formulas on all visible sheets w/o affecting sheet formatting
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" wrote in message ... 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 <atyahoo<dotcom |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Kill all formulas on all visible sheets w/o affecting sheet formatting
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 <atyahoo<dotcom ---- "Jim Cone" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Kill all formulas on all visible sheets w/o affecting sheet formatting
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" wrote in message ... 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 <atyahoo<dotcom |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Kill all formulas on all visible sheets w/o affecting sheet formatting
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 <atyahoo<dotcom ---- "Jim Cone" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
inserting rows affecting formulas on other wkshts | Excel Worksheet Functions | |||
Hide and lock cell formulas without affecting Macro? | Excel Discussion (Misc queries) | |||
Zoom Level Affecting Formatting | Excel Discussion (Misc queries) | |||
kill formatting brought over from reports | Excel Discussion (Misc queries) | |||
HELP!! Cannot set any sheets to visible | Excel Programming |