problems getting this macro to work

  • Thread starter Thread starter stephen.ditchfield
  • Start date Start date
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?
Click to expand...

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
 
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
 
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
 
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.
Click to expand...

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