Programs1234
08-18-2017, 07:36 AM
Having some issues saving down a file with VBA. The error box spits out 'Subscript Out of Range". Anybody know whats wrong?
Sub CATReport()
Dim filename3 As String
Dim LR3 As Integer
Dim column_in_review_2 As Variant
Dim rownumber As Integer
Dim mydate As Date
Dim currentdate As Variant
Dim fname 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(filename3).Activate
Workbooks(filename3).Sheets(1).Cells(16, 2) = currentdate
Workbooks(filename3).Sheets("Key").Select
' Bunch of irrelevant code
pth = "S:\Stock Loan\CPM\CATS\Reporting\Nightly File\2017\"
fname = pth & "CorporateActionsTrackerNightly" & "_" & currentdate & "_Internal Only" & ".xlsx"
Debug.Print fname
Workbooks(filename3).Activate
Workbooks(filename3).SaveAs FileName:=fname, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
'
End Sub
Sub CATReport()
Dim filename3 As String
Dim LR3 As Integer
Dim column_in_review_2 As Variant
Dim rownumber As Integer
Dim mydate As Date
Dim currentdate As Variant
Dim fname 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(filename3).Activate
Workbooks(filename3).Sheets(1).Cells(16, 2) = currentdate
Workbooks(filename3).Sheets("Key").Select
' Bunch of irrelevant code
pth = "S:\Stock Loan\CPM\CATS\Reporting\Nightly File\2017\"
fname = pth & "CorporateActionsTrackerNightly" & "_" & currentdate & "_Internal Only" & ".xlsx"
Debug.Print fname
Workbooks(filename3).Activate
Workbooks(filename3).SaveAs FileName:=fname, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
'
End Sub