Subscript out of range and SaveAs Method error

[Programs1234]

Alright, i got some code here that does not work. Can anyone help me fix this. The error box keeps shooting out the message either "subscript outta range" or "Save as" error. Error happens in the LAST LINE

Please see the code below:

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]

Does CurrentDate contain any illegal filename characters?

[Programs1234]

No sir, currentdate belongs to whatever i plug into the cell of another workbook.

[mdmackillop]

If your date format is 01/02/17 then "/" is an illegal filename character. Copy and post your fname3 value

[Programs1234]

I did not use this "/" character. I mean the current date value is string so i do not believe that is the problem. The error happens at the last line.

[mdmackillop]

Copy and post your fname3 value

[Programs1234]

"CAT Practice Sheet" is what it is called. It is located on another spreadsheet tho.

[mdmackillop]

I see in your code

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

I ask

Copy and post your fname3 value

Can I be any clearer?

[offthelip]

you will get a "subscript out of range " error if the value in filename3 is invalid or the workbook is not open.

So your macro depends totally on what is in

Workbooks("Macros").Sheets(1).Cells(15, 2) and
Workbooks("Macros").Sheets(1).Cells(16, 2)

Since you haven't told us what is in these cells and you haven't posted any workbooks, you are making it very difficult to help you.

[Programs1234]

currentdate is pretty much the date that i would type into another spreadsheet. So if I am running the report today, i would just plug in something like 8.25.2017 so that the report would save with today's date in the name.


"S:\Stock Loan\CPM\CATS\Reporting\Nightly File\2017\" & "CorporateActionsTrackerNightly" & "_" & currentdate & "_Internal Only" & ".xlsx"

Is this what you are asking for?

[offthelip]

i would just plug in something like 8.25.2017 so that the report would save with today's date in the name.

That could be your problem 8.25.2017 is not a standard excel format for a date
try 8/25/2017

[mdmackillop]

Is this what you are asking for?

No. I would expect to see values for all variables and no ampersands if you post the result of "Debug.Print fname3"

[Programs1234]

I mean the currentdate is just a string so i am not too sure if that will fix the problem.

[Programs1234]

"S:\Stock Loan\CPM\CATS\Reporting\Nightly File\2017\CorporateActionsTrackerNightly_ 8.25.2017_Internal Only.xlsx" <-- This is what it should look like.

[mdmackillop]

This is what it should look like

I give up.