Notes

Option Explicit

Sub Run_MIC_Automation_FinalFix()

'--- SETUP ---
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim wsReport As Worksheet, wsUpdatedReport As Worksheet
Dim wsIDs As Worksheet, wsDatascope As Worksheet
Dim wsMicList As Worksheet, wsUpdate As Worksheet
Dim wbNew As Workbook

Dim lLastRow As Long, lLastCol As Long, i As Long
Dim rngData As Range, rngVisible As Range
Dim sSavePath As String, sFileName As String
Dim colIndex As Long
Dim headerName As String
Dim matchFound As Boolean

'PATH: Ensure this path exists exactly as written on your network drive
sSavePath = "T:SMF Daily TaskMIC Code ScrubMIC Code ID Lists"

On Error GoTo ErrorHandler

'--- ASSIGN SHEETS ---
Set wsReport = ThisWorkbook.Sheets("Report")
Set wsUpdatedReport = ThisWorkbook.Sheets("Updated Report")
Set wsIDs = ThisWorkbook.Sheets("IDs to Update")
Set wsDatascope = ThisWorkbook.Sheets("Datascope")
Set wsMicList = ThisWorkbook.Sheets("mic code ID list")
Set wsUpdate = ThisWorkbook.Sheets("Update")

'Reset Filters
If wsReport.AutoFilterMode Then wsReport.AutoFilterMode = False
If wsUpdatedReport.AutoFilterMode Then wsUpdatedReport.AutoFilterMode = False

'Step 1: Clear Contents
wsIDs.Cells.ClearContents
wsDatascope.Cells.ClearContents
wsMicList.Cells.ClearContents
wsUpdate.Cells.ClearContents

'--- FORMATTING ---
colIndex = GetHeaderColumn(wsReport, "Security Alias")
If colIndex > 0 Then wsReport.Columns(colIndex).NumberFormat = "General"

colIndex = GetHeaderColumn(wsUpdatedReport, "Security Alias")
If colIndex > 0 Then wsUpdatedReport.Columns(colIndex).NumberFormat = "General"

'--- FILTER REPORT SHEET (Steps 4-7) ---

lLastRow = wsReport.Cells(wsReport.Rows.Count, 1).End(xlUp).Row
lLastCol = wsReport.Cells(1, wsReport.Columns.Count).End(xlToLeft).Column
Set rngData = wsReport.Range(wsReport.Cells(1, 1), wsReport.Cells(lLastRow, lLastCol))

'Step 4: Filter "Process Secr Type" (FIXED: Removed "=" from criteria)
colIndex = GetHeaderColumn(wsReport, "Process Secr Type")
If colIndex > 0 Then
rngData.AutoFilter Field:=colIndex, Criteria1:="FT*", Operator:=xlOr, Criteria2:="OP*"
End If

'Step 5: Filter "Primary Asset Id Type" (CUSIP)
colIndex = GetHeaderColumn(wsReport, "Primary Asset Id Type")
If colIndex > 0 Then
rngData.AutoFilter Field:=colIndex, Criteria1:="CUSIP"
End If

'Step 6: Filter "Ticker " (Uncheck blanks -> Show Non-Blanks)
colIndex = GetHeaderColumn(wsReport, "Ticker")
If colIndex > 0 Then
rngData.AutoFilter Field:=colIndex, Criteria1:="<>"
End If

'Step 7: Filter "Exchange " (Check blanks -> Show Blanks)
colIndex = GetHeaderColumn(wsReport, "Exchange")
If colIndex > 0 Then
rngData.AutoFilter Field:=colIndex, Criteria1:="="
End If

'Step 8: Copy to IDs to Update
On Error Resume Next
Set rngVisible = rngData.SpecialCells(xlCellTypeVisible)
On Error GoTo ErrorHandler

If Not rngVisible Is Nothing Then
'Check if we have data rows (count > 1 assuming header is 1)
If rngVisible.Count > lLastCol Then
rngVisible.Copy Destination:=wsIDs.Range("A1")
Else
'Do not error, just means no data found
End If
End If

'Keep Filter ON for verification

'--- PROCESS IDs TO UPDATE (Steps 9-11) ---

lLastCol = wsIDs.Cells(1, wsIDs.Columns.Count).End(xlToLeft).Column

For i = lLastCol To 1 Step -1
headerName = Trim(wsIDs.Cells(1, i).Value)
matchFound = False
If StrComp(headerName, "Primary Asset Id", vbTextCompare) = 0 Then matchFound = True
If StrComp(headerName, "Security Alias", vbTextCompare) = 0 Then matchFound = True
If StrComp(headerName, "Ticker", vbTextCompare) = 0 Then matchFound = True
If StrComp(headerName, "Exchange", vbTextCompare) = 0 Then matchFound = True

If matchFound = False Then wsIDs.Columns(i).Delete
Next i

'Step 10: Rename "Exchange " to "Old MIC"
colIndex = GetHeaderColumn(wsIDs, "Exchange")
If colIndex > 0 Then wsIDs.Cells(1, colIndex).Value = "Old MIC"

'Step 11: Add "New MIC"
lLastCol = wsIDs.Cells(1, wsIDs.Columns.Count).End(xlToLeft).Column
wsIDs.Cells(1, lLastCol + 1).Value = "New MIC"

'--- PROCESS UPDATED REPORT (Steps 12-13) ---

lLastRow = wsUpdatedReport.Cells(wsUpdatedReport.Rows.Count, 1).End(xlUp).Row
lLastCol = wsUpdatedReport.Cells(1, wsUpdatedReport.Columns.Count).End(xlToLeft).Column
Set rngData = wsUpdatedReport.Range(wsUpdatedReport.Cells(1, 1), wsUpdatedReport.Cells(lLastRow, lLastCol))

'Step 12: Filter "Process Secr Type" (FIXED: Removed "=")
colIndex = GetHeaderColumn(wsUpdatedReport, "Process Secr Type")
If colIndex > 0 Then
rngData.AutoFilter Field:=colIndex, Criteria1:="EQ*"
End If

'Step 13: Copy to Datascope
On Error Resume Next
Set rngVisible = rngData.SpecialCells(xlCellTypeVisible)
On Error GoTo ErrorHandler

If Not rngVisible Is Nothing Then
rngVisible.Copy Destination:=wsDatascope.Range("A1")
End If

'--- PROCESS DATASCOPE (Step 14) ---

lLastCol = wsDatascope.Cells(1, wsDatascope.Columns.Count).End(xlToLeft).Column

For i = lLastCol To 1 Step -1
headerName = Trim(wsDatascope.Cells(1, i).Value)
matchFound = False
Select Case headerName
Case "Primary Asset Id Type", "Primary Asset Id", "Security Alias", "Currency Cde", "Exchange", "ISIN"
matchFound = True
End Select
If matchFound = False Then wsDatascope.Columns(i).Delete
Next i

'--- PROCESS MIC CODE ID LIST (Steps 15-18) ---

wsDatascope.Range("A:B").Copy Destination:=wsMicList.Range("A1")
wsMicList.Rows(1).Delete
wsMicList.Columns("A").Replace What:="CUSIP", Replacement:="CSP", LookAt:=xlPart
wsMicList.Columns("A").Replace What:="SEDOL", Replacement:="SED", LookAt:=xlPart

sFileName = Format(Date, "YYYYMMDD") & " mic code ID list.csv"
wsMicList.Copy
Set wbNew = ActiveWorkbook

Application.DisplayAlerts = False
wbNew.SaveAs Filename:=sSavePath & sFileName, FileFormat:=xlCSV
wbNew.Close SaveChanges:=False
Application.DisplayAlerts = True

'--- FINAL STEPS (FIXED FOLDER OPENING) ---

'Step 19: Open Folder using FollowHyperlink (Works on T: drive)
On Error Resume Next
ThisWorkbook.FollowHyperlink Address:=sSavePath

'Step 20: Open Website
ThisWorkbook.FollowHyperlink Address:="https://select.datascope.refinitiv.com/DataScope/"
On Error GoTo ErrorHandler

Application.ScreenUpdating = True
Application.DisplayAlerts = True
MsgBox "Automation Complete.", vbInformation

Exit Sub

ErrorHandler:
Application.ScreenUpdating = True
Application.DisplayAlerts = True
MsgBox "Error: " & Err.Description & vbNewLine & "Line: " & Erl
End Sub

'--- HELPER FUNCTION ---
Function GetHeaderColumn(ws As Worksheet, searchHeader As String) As Long
Dim lastCol As Long, i As Long
Dim cellHeader As String

lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

For i = 1 To lastCol
cellHeader = ws.Cells(1, i).Value
'Check Exact
If StrComp(cellHeader, searchHeader, vbTextCompare) = 0 Then
GetHeaderColumn = i
Exit Function
End If
'Check Trimmed
If StrComp(Trim(cellHeader), Trim(searchHeader), vbTextCompare) = 0 Then
GetHeaderColumn = i
Exit Function
End If
Next i
GetHeaderColumn = 0
End Function