View Full Version : Force SaveAs for this macro only

06-23-2016, 05:02 AM
I hope that this is not to dopey but my knowledge of Excel VBA is quite limited.

I have a macro that in essence produces a cut down version of the master file from where it is run that I distribute elsewhere but I need some code at the end that forces me to save the file with a different name.

Yes I know I should keep my wits about me but I have a fear that one day I might overwrite the master file.

So in essence I would like some code that for this macro only forces SaveAs and does not allow me to overwrite the original.

Your help would be most appreciated

06-23-2016, 05:15 AM
Just a thought, but rather, have the SaveAs code at the beginning. Cancelling picking a new filename would result in exiting the macro w/no harm done.

06-23-2016, 05:37 AM
Use SaveCopyAs

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)Cancel = True
End Sub

06-26-2016, 12:05 PM
Thank you both for you're suggestions

I am away from the office for a few days so I won't be able to work on them until then

My sincere apologies for not getting back to you earlier.

Again my thanks

06-26-2016, 06:15 PM
"Produces a cut down version of the master file"

Why not make a new workbook and craft it into the cutdown version.

Your master file with its current name, remains intact.
And the new workbook has to have its name specified the first time it is saved. Let the OS prevent the duplicate naming of the two workbooks.

06-28-2016, 03:52 AM
I need to produce a cut down version at the time that I run the macro because I make changes from time to time and recently forgot to update the second cut down version that I already had resulting in a bit of chaos in the office.

I have been away for a few days hence the delay in getting back to you for which I apologise.

I think that from the bits and pieces that I have found, along with the suggestions above I am close to achieving what I wanted by changing the name at the beginning of the routine. Hopefully I'll have some spare time later in the week to try and finish it.

Again my very sincere thanks to you all for taking an interest