Notes

Option Explicit

Sub Run_MIC_Automation_Fixed()

'--- SETUP & VARIABLES ---
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 headerName As String
Dim colIndex As Variant 'Variant to handle Error if Match fails

'Path Correction: Added backslashes assuming standard Windows path structure based on your T: drive reference
sSavePath = "T:SMF Daily TaskMIC Code ScrubMIC Code ID Lists"

'Set Error Handling
On Error GoTo ErrorHandler

'--- ASSIGN SHEETS ---
'Note: Ensure these sheets exist exactly as named
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 they are currently on
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

'--- STEP 2: REPORT - FORMAT SECURITY ALIAS ---
colIndex = GetColNum(wsReport, "Security Alias")
If colIndex > 0 Then wsReport.Columns(colIndex).NumberFormat = "General"

'--- STEP 3: UPDATED REPORT - FORMAT SECURITY ALIAS ---
colIndex = GetColNum(wsUpdatedReport, "Security Alias")
If colIndex > 0 Then wsUpdatedReport.Columns(colIndex).NumberFormat = "General"

'--- STEPS 4-7: FILTER REPORT SHEET ---
'Define the data range (Assuming headers are in Row 1)
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" for items starting with FT or OP
colIndex = GetColNum(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" for "CUSIP"
colIndex = GetColNum(wsReport, "Primary Asset Id Type")
If colIndex > 0 Then
rngData.AutoFilter Field:=colIndex, Criteria1:="CUSIP"
End If

'Step 6: Filter "Ticker " (With Space) to UNCHECK blank values (Show Non-Blanks)
'Using "<>" is the standard way to filter for "Does not equal empty"
colIndex = GetColNum(wsReport, "Ticker ")
If colIndex > 0 Then
rngData.AutoFilter Field:=colIndex, Criteria1:="<>"
End If

'Step 7: Filter "Exchange " (With Space) to CHECK only blank values (Show Blanks)
'Using "=" is the standard way to filter for "Equals empty"
colIndex = GetColNum(wsReport, "Exchange ")
If colIndex > 0 Then
rngData.AutoFilter Field:=colIndex, Criteria1:="="
End If

'--- STEP 8: COPY TO IDs TO UPDATE ---
'Check if there are visible cells (excluding header)
On Error Resume Next
Set rngVisible = rngData.SpecialCells(xlCellTypeVisible)
On Error GoTo ErrorHandler

If Not rngVisible Is Nothing Then
rngVisible.Copy Destination:=wsIDs.Range("A1")
Else
MsgBox "No records matched the filter criteria on the Report sheet."
End If

'Turn off filter on Report
wsReport.AutoFilterMode = False

'--- STEPS 9-11: PROCESS IDs TO UPDATE ---

'Step 9: Remove columns EXCEPT specific ones
lLastCol = wsIDs.Cells(1, wsIDs.Columns.Count).End(xlToLeft).Column

'Loop backwards to delete columns safely
For i = lLastCol To 1 Step -1
headerName = wsIDs.Cells(1, i).Value
'We use strict comparison here to respect the spaces you mentioned
Select Case headerName
Case "Primary Asset Id", "Security Alias", "Ticker ", "Exchange "
'Do Nothing - Keep these
Case Else
wsIDs.Columns(i).Delete
End Select
Next i

'Step 10: Change "Exchange " to "Old MIC"
colIndex = GetColNum(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"

'--- STEPS 12-13: PROCESS UPDATED REPORT ---

'Define range for Updated Report
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" for items beginning with "EQ"
colIndex = GetColNum(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

wsUpdatedReport.AutoFilterMode = False

'--- STEP 14: PROCESS DATASCOPE COLUMNS ---
lLastCol = wsDatascope.Cells(1, wsDatascope.Columns.Count).End(xlToLeft).Column

For i = lLastCol To 1 Step -1
headerName = wsDatascope.Cells(1, i).Value
'Note: Check if the prompt implied spaces here. Assuming standard based on prompt list:
Select Case headerName
Case "Primary Asset Id Type", "Primary Asset Id", "Security Alias ", "Currency Cde", "Exchange ", "ISIN "
'Keep these (Added spaces to Security Alias, Exchange, ISIN based on prompt pattern if needed, adjust if exact match fails)
Case Else
wsDatascope.Columns(i).Delete
End Select
Next i

'--- STEPS 15-18: PROCESS MIC CODE ID LIST ---

'Step 15: Copy first two columns (Primary Asset Id Type, Primary Asset Id)
wsDatascope.Range("A:B").Copy Destination:=wsMicList.Range("A1")

'Step 16: Remove top row
wsMicList.Rows(1).Delete

'Step 17: Replace CUSIP->CSP and SEDOL->SED in Column A
wsMicList.Columns("A").Replace What:="CUSIP", Replacement:="CSP", LookAt:=xlPart
wsMicList.Columns("A").Replace What:="SEDOL", Replacement:="SED", LookAt:=xlPart

'Step 18: Save as CSV
sFileName = Format(Date, "YYYYMMDD") & " mic code ID list.csv"

wsMicList.Copy 'Creates new workbook
Set wbNew = ActiveWorkbook

'Suppress overwrite prompt if file exists
Application.DisplayAlerts = False
wbNew.SaveAs Filename:=sSavePath & sFileName, FileFormat:=xlCSV
wbNew.Close SaveChanges:=False
Application.DisplayAlerts = True 'Turn back on immediately

'--- STEPS 19-20: OPEN FOLDER AND WEB ---

'Step 19: Open Folder
On Error Resume Next
Shell "explorer.exe " & sSavePath, vbNormalFocus

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

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

Exit Sub

ErrorHandler:
Application.ScreenUpdating = True
Application.DisplayAlerts = True
MsgBox "An error occurred: " & Err.Description & vbNewLine & "Step likely failed at line: " & Erl
End Sub

'--- HELPER FUNCTION ---
Function GetColNum(ws As Worksheet, colName As String) As Long
'Finds the EXACT column header (Case sensitive if needed, currently not case sensitive but space sensitive)
Dim rngFound As Range

'Look in the first row
Set rngFound = ws.Rows(1).Find(What:=colName, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)

If Not rngFound Is Nothing Then
GetColNum = rngFound.Column
Else
MsgBox "Could not find column: [" & colName & "] on sheet: " & ws.Name & vbNewLine & _
"Check that the header name is exact (including spaces).", vbCritical
GetColNum = 0
End If
End Function