This version of the macro will recognize the workbook name based only on the month so you don't have to change the information in the macro for the month and the day as long as you run it on the correct day meaning that if you want to update the "22" sheet, you would have to run it today because it is the 22nd today.
Sub CopyData()
Application.ScreenUpdating = False
Dim WB As Workbook, srcWS As Worksheet, desWS As Worksheet, lRow As Long, v As Variant, v2 As Variant, i As Long, rName As Range, x As Variant
Set srcWS = Workbooks("Counts.xlsx").Sheets("Data")
For Each WB In Workbooks
If WB.Name Like "*" & MonthName(Month(Date)) & "*" Then
Set desWS = Workbooks(WB.Name).Sheets(Day(Date))
Exit For
End If
Next WB
lRow = desWS.Range("A" & Rows.Count).End(xlUp).Row - 1
desWS.Range("C2:L" & lRow).ClearContents
v = srcWS.Range("A4", srcWS.Range("A" & Rows.Count).End(xlUp)).Resize(, 3).Value
v2 = desWS.Range("A2", desWS.Range("A" & Rows.Count).End(xlUp)).Value
For i = LBound(v) To UBound(v)
Set rName = desWS.Rows(1).Find(v(i, 1), LookIn:=xlValues, lookat:=xlWhole)
x = Application.Match(v(i, 2), v2, 0)
If Not IsError(x) Then
desWS.Cells(x + 1, rName.Column) = v(i, 3)
End If
Next i
Application.ScreenUpdating = True
End Sub
Bookmarks