Ctrl + Home on all worksheets

[Guest]

I found the below code (compliments of Tom Ogilvy) that works great for what
I'm trying to do. I would just like one last step that I can't figure out.
I want to convert all formulas to values in all sheets, which is what the
code below does. When the code is done running, however, all the sheets are
still highlighted (selected). I would like to unselect all these ranges and
return to the active sheet. Basically, I would like the workbook to look
exactly the same after running the macro, minus the formulas. I'm sure this
is probably a very simple fix, but I'm brand new to vba and don't know it.

Sub convert_to_values()
'Removes ALL formulas and replaces them with valves,
'for each sheet in your workbook
Application.ScreenUpdating = False
Dim Sht As Worksheet
For Each Sht In ThisWorkbook.Worksheets
sht.Cells.copy
sht.Cells.PasteSpecial xlValues
Next Sht
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub



Thanks,

Jeff

 

[Guest]

Try this...

Sub convert_to_values()
'Removes ALL formulas and replaces them with valves,
'for each sheet in your workbook
Application.ScreenUpdating = False
Dim Sht As Worksheet
Dim rng as Range
For Each Sht In ThisWorkbook.Worksheets
sht.select
set rng = activecell
sht.Cells.copy
sht.Cells.PasteSpecial xlValues
rng.select
set rng = notihing
Next Sht
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

 

[Guest]

Typo... Notihing should be Nothing

 

[Guest]

Thanks,
this looks like it will do the trick!

Try this...

Sub convert_to_values()
'Removes ALL formulas and replaces them with valves,
'for each sheet in your workbook
Application.ScreenUpdating = False
Dim Sht As Worksheet
Dim rng as Range
For Each Sht In ThisWorkbook.Worksheets
sht.select
set rng = activecell
sht.Cells.copy
sht.Cells.PasteSpecial xlValues
rng.select
set rng = notihing
Next Sht
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub