VBA Index and Match with dynamic workbook name-CodePudding

I am new to VBA and wanted to automate dashboards (Excel reports) that I do daily. One step I do is to add comments to each of the funds. The dashboards are updated each day but there will be some overlap from the previous day so I want to an Index and Match function on the previous day's dashboards to find the comments.

Daily - the name of the daily dashboard where I am performing the macro

Daily Dashboard_09.09.21 - the name I save the previous day's dashboard as, with the date changing each day

The problem is that we do not complete these dashboards over the weekend and so Monday's dashboard will need to refer to Friday's dashboard for the Index and Match. Therefore, I thought the easiest way to tackle this would be to do a Name like and use the wildcards *.

My code below works perfectly when I use the exact name of the previous day's dashboard when setting wb2. However, when I try to use Name like and use ActiveWorkbook, it does not recognise the previous day's dashboard as the ActiveWorkbook and the Index and Match returns nothing.

Please could you help me correct this?

Dim wb As Workbook
Dim wb1 As Workbook
Dim wb2 As Workbook

Dim wbName As String
wbName = "Daily Dashboard"
For Each wb In Applications.Workbooks
If wb.Name Like wbName & "*" Then
wb.Activate
Exit For
End If
Next wb

Set wb1 = Workbooks("Daily")
Set wb2 = ActiveWorkbook

Dim ws1 As Worksheet
Dim ws2 As Worksheet

Set ws1 = wb1.Worksheets("Fund Loan Details")
Set ws2 = wb2.Worksheets("Fund Loan Details")

Dim End_Row_1 As Long
Dim End_Row_2 As Long

End_Row_1 = ws1.Range("C" & Rows.Count).End(xlUp).Row
End_Row_2 = ws2.Range("C" & Rows.Count).End(xlUp).Row

For Counter = 3 to End_Row_1

On Error Resume Next

If Range("T" & Counter) = "Y" And Range("U" & Counter) = "" Then
ws1.Cells(Counter, 21). Value = Applications.WorksheetFunction.Index(ws2.Range("U3:U" & End_Row_2), Application.WorksheetFunction.Match(ws1.Cells(Counter, 3). Value, ws2.Range("C3:C" & End_Row_2), 0))

CodePudding user response：

Check if a match was found and if there was no match exit the sub.

Const pattern = "Daily Dashboard*"
Dim wbName as String, wb as Workbook
For Each wb In Applications.Workbooks
     If wb.Name Like pattern Then
         wbName = wb.Name
         Exit For
     End If
Next wb

If wbName = "" then
    Msgbox "No workbook like " & pattern, vbCritical
    Exit Sub
Else
    Set wb2 = Workbooks(wbName)
End If