Ctrl + Home on all worksheets

[goofy11]

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

[Jim Thomlinson]

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

--
HTH...

Jim Thomlinson


"goofy11" wrote:

> 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
>

[Jim Thomlinson]

Typo... Notihing should be Nothing
--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

> 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
>
> --
> HTH...
>
> Jim Thomlinson
>
>
> "goofy11" wrote:
>
> > 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
> >

[goofy11]

Thanks,
this looks like it will do the trick!

"Jim Thomlinson" wrote:

> 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
>
> --
> HTH...
>
> Jim Thomlinson
>
>
> "goofy11" wrote:
>
> > 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
> >