Save Button isn't working

[sassriverrat]

I have a save button that's supposed to save a file and then, based on the name of the file, delete the old copy.

When I originally had the coding set to send a msgbox about where it was being saved, the button worked. Then I changed the coding to a yesno msgbox and I added the kill piece for deleting the other file and now the button doesn't react at all- I can click it and the macro doesn't do anything (I don't know if it's even running- the button clicks and pops back out). Thanks!

Private Sub SaveAsDirectory()'Creates the SaveAs "#L/B and Ports" on the Arrival Sheet


    Dim Path1 As String
    Dim Path2 As String
    Dim Path3 As String
    Dim Path4 As String
    Dim Path5 As String
    Dim path6 As String
    Dim Path7 As String
    Dim Path8 As String
    Dim myfilename As String
    Dim fpathname As String
    Dim oldpathme As String
    Dim int1 As Integer:
    Dim int2 As Integer:
    Dim path As String:
    Dim x As Integer:
    Dim fldr As String:
    Dim resp As Integer
    Path1 = Worksheets("Notes").Range("O26")
    Path2 = Worksheets("Notes").Range("P26")
    Path3 = Worksheets("Notes").Range("Q26")
    Path4 = Worksheets("Notes").Range("R26")
    Path5 = Worksheets("Notes").Range("S26")
    Path7 = Worksheets("Notes").Range("O17")
    Path8 = Worksheets("Notes").Range("O19")
    int1 = Worksheets("Notes").Range("T23")
    int2 = Worksheets("Notes").Range("O26")
    path = Worksheets("Notes").Range("N22")
    x = Int((int2 - 1) / int1) * int1
    fldr = 1 + x & "-" & int1 + x
    
    myfilename = Path1 & Path2 & " " & Path3 & Path4 & Path5
    fpathname = path & "\" & fldr & "\" & myfilename & ".xlsm"
    oldpathme = Path7 & "\" & Path8 & ".xlsm"
    
    ActiveSheet.EnableCalculation = False
    
    If ActiveWorkbook.Name = oldpathme Then
        resp = MsgBox("You are trying to save voyage " & myfilename & " to:" & vbCrLf & fpathname & vbCrLf & vbCrLf & "Current Voyage Report will be archived and the Master Voyage Report reset for next voyage. Thanks for using the OSG Voyage Reporting System!" & vbCrLf & vbCrLf & "File: " & int2 & vbTab & "Folder: " & fldr & vbCr & path & "\" & fldr, vbYesNo)
            If resp = vbYes Then
                If Len(Dir(path & "\" & fldr, vbDirectory)) = 0 Then MkDir path & "\" & fldr
                    ActiveWorkbook.SaveAs Filename:=fpathname, FileFormat:=xlOpenXMLWorkbookMacroEnabled
            End If
        'Call File Killer
            Kill (oldpathme)
        'Application Closer
            If Workbooks.Count > 1 Then
                ActiveWorkbook.Close
            Else: Application.Quit
            End If
    ElseIf ActiveWorkbook.Name = fpathname Then
        ActiveWorkbook.Save
        'Application Closer
            If Workbooks.Count > 1 Then
                ActiveWorkbook.Close
            Else: Application.Quit
            End If
    ElseIf ActiveWorkbook.Name = "Master Voyage Report" Then
       resp = MsgBox("You are trying to save voyage " & myfilename & " to:" & vbCrLf & fpathname & vbCrLf & vbCrLf & "Current Voyage Report will be archived and the Master Voyage Report reset for next voyage. Thanks for using the OSG Voyage Reporting System!" & vbCrLf & vbCrLf & "File: " & int2 & vbTab & "Folder: " & fldr & vbCr & path & "\" & fldr, vbYesNo)
           If resp = vbYes Then
                If Len(Dir(path & "\" & fldr, vbDirectory)) = 0 Then MkDir path & "\" & fldr
                    ActiveWorkbook.SaveAs Filename:=fpathname, FileFormat:=xlOpenXMLWorkbookMacroEnabled
            End If
        'Application Closer
            If Workbooks.Count > 1 Then
                ActiveWorkbook.Close
            Else: Application.Quit
            End If
    End If
End Sub

 

[Yongle]

1. (When a workbook has been saved) ActiveWorkbook.Name returns the name including extention...
so test below is not correct

ElseIf ActiveWorkbook.Name = "Master Voyage Report" Then

Try either ...

ElseIf Left(ActiveWorkbook.Name, InStrRev(ActiveWorkbook.Name, ".") - 1) = "Master Voyage Report" Then

... or add the correct extension

ElseIf ActiveWorkbook.Name = "Master Voyage Report" & ".xlsm" Then

2. Also another test that is not correct

ElseIf ActiveWorkbook.Name = fpathname Then

fpathname contains path + name of workbook + extension
whereas
ActiveWorkbook.Name = name + extension
ActiveWorkbook.FullName = path + name of workbook + extension

try

ElseIf ActiveWorkbook.FullName = fpathname Then

3. Learn to use Debug.Print
You need to be able to test values as the code runs. One way is with Debug.Print
Add this line in your code immediately above the ElseIF test

 Debug.Print ActiveWorkbook.Name

see the result in the immediate window with {CTRL} G (or click View \ Immediate Window)

 

[Yongle]

Instead of

    Path1 = Worksheets("Notes").Range("O26")
    Path2 = Worksheets("Notes").Range("P26")
    Path3 = Worksheets("Notes").Range("Q26")
    Path4 = Worksheets("Notes").Range("R26")
    Path5 = Worksheets("Notes").Range("S26")
    Path7 = Worksheets("Notes").Range("O17")
    Path8 = Worksheets("Notes").Range("O19")
    int1 = Worksheets("Notes").Range("T23")
    int2 = Worksheets("Notes").Range("O26")
    path = Worksheets("Notes").Range("N22")

... avoid repetition like this

    With Worksheets("Notes")
        Path1 = .Range("O26")
        Path2 = .Range("P26")
        Path3 = .Range("Q26")
        Path4 = .Range("R26")
        Path5 = .Range("S26")
        Path7 = .Range("O17")
        Path8 = .Range("O19")
        int1 = .Range("T23")
        int2 = .Range("O26")
        path = .Range("N22")
    End With

 

[sassriverrat]

brilliant! I'll give it a shot. The repetition I should have thought about- very much still green but learning (and enjoying!) quickly. I learned quickly watching how excel recorded that I could cut down a lot of that!