On Nov 27, 8:57*am, Per Jessen wrote:
The event macro will be called when MySave macro is executed. To avoid
the call to the Workbook_BeforeSave event, you can use the code as
below:

'*** In the module insert use this ***
'*** Notice that the Public declared variable has to be at the very
top of the module***
Public DisableSaveEvent As Boolean
Sub MySave()
Dim MyFileName As String
ChDir "S:\CCAS\DC\Reablement Team Goal Plans Etc\2 SPICE SHEETS"
MyFileName = Application.GetSaveAsFilename

If MyFileName = "false" Then Exit Sub
DisableSaveEvent = True
ActiveWorkbook.SaveAs MyFileName
End Sub

'*** In ThisWorkbook module use this code ***
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
If DisableSaveEvent Then Exit Sub
ChDir "C:\Temp" 'Path to save copy to
Do
* * fName = Application.GetSaveAsFilename("", fName =
Application.GetSaveAsFilename("", "Excel Files (*.xls), *.xls", ,
"Enter filename"), , "Enter filename")
Loop Until fName < "false"
Debug.Print fName
ThisWorkbook.SaveCopyAs fName
Cancel = True
End Sub

Regards,
Per



Hi

thanks - cant get it work yet *- might it being in conflict with
another macro in the "module"

Sub MySave()

Dim MyFileName As String
ChDir "S:\CCAS\DC\Reablement Team Goal Plans Etc\2 SPICE SHEETS"
MyFileName = Application.GetSaveAsFilename
If MyFileName = "false" Then Exit Sub
ActiveWorkbook.SaveAs MyFileName
End Sub

thanks

Johnny- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

Thanks - will check it out in work next week and let you know how I
get on.

much appreciated

John