Hi All
I am using the following code to save files into folders based on cell values.
If the file name exists this line gives a Yes No Cancel MsgBox to overwrite or not.
Clicking Yes overwrites the file but No or Cancel throw an error and creates a new workbook.
Can I cancel the whole procedure if No or Cancel are selected so that a new workbook is not created and the user gets a MsgBox that nothing happened?
Thanks in advance for any help
maxwell13
I am using the following code to save files into folders based on cell values.
VBA Code:
Sub makeDir()
Application.ScreenUpdating = False
Dim str As String
ActiveSheet.Copy
Range("A1").Select
str = "C:\" & Range("a2")
If Len(Dir(str, vbDirectory)) = 0 Then
MkDir str
End If
str = str & "\" & Range("a3")
If Len(Dir(str, vbDirectory)) = 0 Then
MkDir str
End If
str = str & "\" & Range("a4")
If Len(Dir(str, vbDirectory)) = 0 Then
MkDir str
End If
str = str & "\" & Range("a5")
If Len(Dir(str, vbDirectory)) = 0 Then
MkDir str
End If
ChDir str
ActiveWorkbook.SaveAs Filename:=str & "\" & Range("a7").Text, FileFormat:=51
Range("a1").Select
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.ScreenUpdating = True
End Sub
If the file name exists this line gives a Yes No Cancel MsgBox to overwrite or not.
VBA Code:
ActiveWorkbook.SaveAs Filename:=str & "\" & Range("a7").Text, FileFormat:=51
Clicking Yes overwrites the file but No or Cancel throw an error and creates a new workbook.
Can I cancel the whole procedure if No or Cancel are selected so that a new workbook is not created and the user gets a MsgBox that nothing happened?
Thanks in advance for any help
maxwell13