Subscript out of range and SaveAs Method error (Revised)

[Programs1234]

I recently made a post about this issue earlier but i felt i was not very clear on what i needed help with. I am running this macro and it seems to have an error at the very last line. The error box shows either "Subscript out of range" or "Save As Method Failed".

I have some code below that I feel is relevant to the error.

Please Note:

filename3 is simply the file name i give the current file of which i will run this macro on.
currentdate is the date that i plan on attaching to the file.

Both of these variables are strings.

Sub CATReport() 
     
    Dim filename3 As Variant 
    Dim LR3 As Integer 
    Dim column_in_review_2 As Variant 
    Dim rownumber As Integer 
    Dim mydate As Date 
    Dim currentdate As Variant 
    Dim fname3 As Variant 
    Dim filename4 As Variant 
     
     
     
     'Attach Buttons from Macro sheet
     
    LR3 = Range("AC" & Rows.Count).End(xlUp).Row 
     
    Application.DisplayAlerts = False 
     
    Workbooks("Macros").Activate 
     
     
    filename3 = Workbooks("Macros").Sheets(1).Cells(15, 2) 
     
    Workbooks("Macros").Activate 
     
    currentdate = Workbooks("Macros").Sheets(1).Cells(16, 2) 
     
    Workbooks(filename3).Activate 
     
     
     ''''''''''Irrelevant code'''''''''''''''
     
     
     
    pth = "S:\Stock Loan\CPM\CATS\Reporting\Nightly File\2017\" 
     
    fname3 = pth & "CorporateActionsTrackerNightly" & "_" & currentdate & "_Internal Only" & ".xlsx" 
    Debug.Print fname3 
     
     
    Workbooks(filename3).SaveAs FileName:=fname3, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False 
     
End Sub

[mdmackillop]

This is not a new question. I've asked 4 times for the debug.print value of fname3 (not what it should be) If you won't supply that, I'll close both questions.

[offthelip]

While you are doing that what about adding a Debug.Print filename3 too so that we can see what workbook name you are referencing as well.

[Paul_Hossler]

I'm guessing that your index into the Workbooks collection is incorrect in that it should not include the Path ( "S:\Stock Loan\CPM\CATS\Reporting\Nightly File\2017\")

fname3 = pth & "CorporateActionsTrackerNightly" & "_" & currentdate & "_Internal Only" & ".xlsx" 

 Workbooks("CorporateActionsTrackerNightly" & "_" & currentdate & "_Internal Only" & ".xlsx").SaveAs FileName:=fname3, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

[snb]

Instead of irrelevant code, you'd better use Code Tags.
You don't need any variables in this case.

[Programs1234]

mdmackillop,

I apologize for the confusion of which I have caused. I am a beginner at Excel VBA, thus my knowledge and understanding of this topic is quite limited. I appreciate your patience and willingness to help.

Regarding your question for the debug.print value of the "fname3" variable.

It is: S:\Stock Loan\CPM\CATS\Reporting\Nightly File\2017\CorporateActionsTrackerNightly__Internal Only.xlsx

Does this answer your question?

[Paul_Hossler]

And did #4 help?

[Programs1234]

I tried copying and pasting the code of which you have listed into my macro. It did not fix the problem.

[mdmackillop]

Replace the latter part of your code with this.

    pth = "S:\Stock Loan\CPM\CATS\Reporting\Nightly File\2017\"
    fname3 = pth & "CorporateActionsTrackerNightly" & "_" & currentdate & "_Internal Only" & ".xlsx"
    p = Left(pth, Len(pth) - 1)
    If Dir(p, vbDirectory) = "2017" Then
        ActiveWorkbook.SaveAs Filename:=fname3, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    Else
        MsgBox "folder not found"
    End If

However, a value for currentdate does not appear in fname3. Did you forget to fill in the cell? This could still be the problem with your code and I would like to see the full fname3 which includes the currentdate value.
How have you ensured that current date can only contain a legitimate value, if entered by others?

[Programs1234]

I found the solution to this issue. It had absolutely nothing to do with the structuring of my code and everything to do with the error I made when I defined what the variable pth is equal to. On the code listed above, pth =
"S:\Stock Loan\CPM\CATS\Reporting\Nightly File\2017\"

What is important to note is that the "Reporting" tab does not exist. I took out the reporting tab and the macro ran properly. Thank you to everyone for your efforts and help!