Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / February 2006

Tip: Looking for answers? Try searching our database.

Ctrl + Home on all worksheets

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
goofy11 - 21 Feb 2006 23:31 GMT
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 - 22 Feb 2006 00:11 GMT
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

Signature

HTH...

Jim Thomlinson

> 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.  
[quoted text clipped - 21 lines]
>
> Jeff
Jim Thomlinson - 22 Feb 2006 00:17 GMT
Typo... Notihing should be Nothing
Signature

HTH...

Jim Thomlinson

> Try this...
>
[quoted text clipped - 41 lines]
> >
> > Jeff
goofy11 - 22 Feb 2006 01:08 GMT
Thanks,
this looks like it will do the trick!

> Try this...
>
[quoted text clipped - 41 lines]
> >
> > Jeff
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.