Saving Problem
On Jul 31, 1:02*pm, "Bob Phillips" wrote:
You don't need all of that extraneous gumpf, it inherits it from the
original workbook
Sub BkupSve()
* * Application.ScreenUpdating = False
* * ActiveSheet.Protect DrawingObjects:=False, _
* * * * * * * * * * * * Contents:=False, _
* * * * * * * * * * * * Scenarios:=False
* * Range("C69").Copy
* * ActiveSheet.Paste
* * Application.CutCopyMode = False
* * On Error Resume Next
* * Call Hide
* * Application.DisplayAlerts = False
* * With ActiveWorkbook
* * * * .Save
* * * * .SaveCopyAs Filename:=.Worksheets("Confidence
Levels").Range("C69").Value & ".xls"
* * End With
* * Call firefighter
* * MsgBox "File successfully backed up. *Now in Read Only. " & _
* * * * * *"Select Change User to Edit"
End Sub
--
__________________________________
HTH
Bob
"don" wrote in message
...
On Jul 31, 11:16 am, don wrote:
On Jul 31, 10:46 am, "Bob Phillips" wrote:
You save ThisWorkbook, but SaveAs Activeworkbook. Can you be sure they
are
the same workook?
It would also be better to use SaveCopyAs than SaveAs, the original book
stays in memory then.
--
__________________________________
HTH
Bob
"don" wrote in message
....
Hi Group,
Hi have a custom save routine which saves to a local machine, then
creates a backup save to a network drive the path of which is defined
within the worksheet. During testing it has worked many times over
the network but users report that it doesn't always save, and more
peculiarly that only part of the information changed is saved! The
time a backup save takes is around 4 minutes over the network.
I have identified some occurrences of user error but am unable to
explain all. I've posted code used below and would appreciate any
comments.
Don
Sub BkupSve()
Application.ScreenUpdating = False
ActiveSheet.Protect DrawingObjects:=False, Contents:=False,
Scenarios:=False
Range("C69").Select
Selection.Copy
ActiveSheet.Paste
Application.CutCopyMode = False
On Error Resume Next
Call Hide
Application.DisplayAlerts = False
ThisWorkbook.Save
With ActiveWorkbook
.SaveAs Filename:="" _
& .Worksheets("Confidence Levels").Range("C69").Value &
".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="",
_
ReadOnlyRecommended:=False, CreateBackup:=False
End With
Call firefighter
MsgBox "File successfully backed up. Now in Read Only. Select Change
User to Edit"
End Sub- Hide quoted text -
- Show quoted text -
Bob,
Thanks for reply.
There could be four worbooks open red white blue green, but on the
occasion I'm now investigating only one was open when the backup was
done. I presume from what you have said it would be better to change
this workbook for activeworkbook?
Could the save as be responsible for not all changes being saved?
Revised code
Sub BkupSve()
Application.ScreenUpdating = False
ActiveSheet.Protect DrawingObjects:=False, Contents:=False,
Scenarios:=False
Range("C69").Select
Selection.Copy
ActiveSheet.Paste
Application.CutCopyMode = False
On Error Resume Next
Call Hide
Application.DisplayAlerts = False
ActiveWorkbook.Save
With ActiveWorkbook
.SaveCopyAs Filename:="" _
& .Worksheets("Confidence Levels").Range("C69").Value &
".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="",
_
ReadOnlyRecommended:=False, CreateBackup:=False
End With
Call firefighter
MsgBox "File successfully backed up. Now in Read Only. Select Change
User to Edit"
End Sub
?- Hide quoted text -
- Show quoted text -
Using my sample in last message generated a Named argument not found
error on the File format line, Can you let me know what I missed out
please.- Hide quoted text -
- Show quoted text -
Thank you
|