PDA

View Full Version : Subscript out of range and SaveAs Method error



Programs1234
08-24-2017, 10:49 AM
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
08-24-2017, 10:59 AM
Does CurrentDate contain any illegal filename characters?

Programs1234
08-24-2017, 11:10 AM
No sir, currentdate belongs to whatever i plug into the cell of another workbook.

mdmackillop
08-24-2017, 11:16 AM
If your date format is 01/02/17 then "/" is an illegal filename character. Copy and post your fname3 value

Programs1234
08-24-2017, 11:45 AM
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
08-24-2017, 12:11 PM
Copy and post your fname3 value

Programs1234
08-25-2017, 08:51 AM
"CAT Practice Sheet" is what it is called. It is located on another spreadsheet tho.

mdmackillop
08-25-2017, 01:46 PM
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
08-25-2017, 03:09 PM
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
08-28-2017, 08:41 AM
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
08-28-2017, 08:54 AM
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
08-28-2017, 09:12 AM
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
08-28-2017, 11:49 AM
I mean the currentdate is just a string so i am not too sure if that will fix the problem.

Programs1234
08-28-2017, 11:50 AM
"S:\Stock Loan\CPM\CATS\Reporting\Nightly File\2017\CorporateActionsTrackerNightly_ 8.25.2017_Internal Only.xlsx" <-- This is what it should look like.

mdmackillop
08-28-2017, 01:03 PM
This is what it should look like
I give up.