problems getting this macro to work

[GS]

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

 

[GS]

OR...

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
With wks
.Unprotect Password:=""
.UsedRange.Value = .UsedRange.Value
.Protect Password:=""
End With
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

Don't forget to put your actual password between the quotes!

--
Garry

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

 

[stephen.ditchfield]

Hi Garry
that is fantastic,
worked like a charm and thank you for your time and knowledge with helping me.
It is very much appreciated and I am very slowly learning.

regards
Ditchy

 

[GS]

Hi Garry

that is fantastic,
worked like a charm and thank you for your time and knowledge with
helping me.
It is very much appreciated and I am very slowly learning.

That's great!
I appreciate the feedback...

--
Garry

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