Programs1234
08-28-2017, 01:57 PM
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
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