Save as new filename function

[rama4672]

I have a function that looks at the file name of the workbook and increments it by 1.
This was working but now i get an error message, it says compile error sub or function not defined.
Here is the code it refers to

Function Increment_Filename() As String
' if the filename of the current activeworkbook contains a numeric
' suffix then the suffix is incremented by 1 and the full path and filename
' are returned.
' if not then the filename is returned with a numeric suffix of 1.
' if the file has not yet been saved then 'Not Saved' is returned.
' ASSUMPTION: that the workbook has a 3 character extension
Dim newPath As String: Dim baseFileName As String: Dim Extension As String: Dim i As Integer
newPath = ActiveWorkbook.Path & "\"
'return Not Saved if path is empty
If newPath = "\" Then Increment_Filename = "Not Saved": Exit Function
Extension = Right(ActiveWorkbook.Name, 4)
baseFileName = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4)
For i = Len(baseFileName) To 1 Step -1
If Mid(baseFileName, i, 1) < "0" Or Mid(baseFileName, i, 1) > "9" Then
Exit For
End If
x = Mid(baseFileName, i, 1) & x 'build numeric suffix
Next i
Increment_Filename = newPath & Left(baseFileName, Len(baseFileName) - Len(x)) & (Val(x) + 1) & Extension
End Function

Private Sub SaveAs()
newfile = Increment_Filename
If newfile = "Not Saved" Then MsgBox "Save File First.": Exit Sub
save As newfile
End Sub

then i put a command button on with the following code:

Private Sub CommandButton18_Click()
SaveAs
End Sub

Thank you if anyone can shed some light on this

Regards

Ian

[mdmackillop]

A space has appeared on this line which changes the meaning
save As newfile

try

Private Sub SaveAs()
    newfile = Increment_Filename
    If newfile = "Not Saved" Then MsgBox "Save File First.": Exit Sub
    ActiveWorkbook.SaveAs newfile
End Sub

[rama4672]

Thank you for the reply MD, I have tried changing it but it still gives me the same message.


Regards

Ian

[mdmackillop]

In the VB Editor, Click on Debug/CompileVBAProject. What function is highlighted?

[rama4672]

When i do that this is the file that it highlights

Private Sub CommandButton18_Click()
SaveAs
End Sub

It is the SaveAs that is highlighted

Regards

Ian

[johnske]

Hi Ian,

I think you'll find you're using a reserved word as the name for one of your procedures and that's why you're getting the error messages i.e. > Private Sub SaveAs() .
Try changing this to Private Sub SaveIt() or Private Sub SaveThis() ... etc.

Regards,
John

[Jacob Hilderbrand]

Private Sub SaveAs()

Private Subs are Private. So you cannot call them by simply typing the name of the sub.

Try changing the Sub to this.

Sub SaveAs()

Or

Public SaveAs()

Or use Run to call the macro.

Application.Run "SaveAs"

[rama4672]

Thanks for all the input, but i have tried all these and it is still not working

If I go to an earlier copy of the file that procedure works ok, I have even copied the modules that contain the macro over to the latest version of the file.
I think the version that you have got Jake it works with, i have not done much more with it other than change the menu to a userform and put a password on to it

Thanks

Ian

[rama4672]

Thank you to everyone who helped with this, but it has now been solved with the following code:

Private Sub SaveFile()
    Dim NewFileName As String
    NewFileName = Increment_Filename
    If NewFileName = "Not Saved" Then msgbox "Save File First.": Exit Sub
    ActiveWorkbook.SaveAs (NewFileName)
End Sub

I then placed the code in the same module as the CommandButton_click are housed and everything is now working as it should

Regards

Ian