I have managed to get this to work, but only with an unprotected
sheet, when protected it comes up with a error (400) and saves it to
the desktop with the name (book 6)not the workbook name. Is there a
work around?

When you set protection VBA code can't modify cells unless you specify
'UserInterfaceOnly=True' (non-persistent between sessions). You must do
this via code on unprotected sheets. That means the sheet protection
needs to be 'toggled' if existing, every time the file is opened.
Otherwise...

Sub ConvertToValues()
Dim wkbTarget As Workbook, wks, sFile$
Const sExt$ = ".xls" '//edit to suit

'Copy sheets to new workbook
ActiveWindow.SelectedSheets.Copy
Set wkbTarget = ActiveWorkbook

'Convert to values
For Each wks In wkbTarget.Worksheets
wks.Unprotect Password:=""
With wks.UsedRange: .Value = .Value: End With
wks.Protect Password:=""
Next 'wks

'At this point wkbTarget has not been saved,
'so timestamp a copy of it then close it.
TimeStampFile SavePath:="C:\Work Related Data", _
Filename:="MyFilename" & sExt
wkbTarget.Close SaveChanges:=False
'Cleanup
Set wkbTarget = Nothing
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion