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!