This should help. The sheet name, file path and ranges within the code needs to be changed to whatever you have.
This first code is designed to run when you click a button or manually tell it to run and will look at each cell in the range B7:B10.
The code should be added to a normal module within your daily tracker file (Alt+F11 to open the VBE and then Insert > Module).
Option Explicit 'This line needs to go at the very top of the module.
Public Sub TransferData()
'Grab the date from the sheet.
Dim OffDate As Date
OffDate = ThisWorkbook.Worksheets("Sheet1").Range("B1")
'Get the full month name from the date.
Dim OffMonth As String
OffMonth = Format(OffDate, "mmmm")
Dim OffDay As Long
OffDay = Day(OffDate)
'Look at each cell in the range in turn.
Dim wrkBk As Workbook
Dim Cell As Range
For Each Cell In ThisWorkbook.Worksheets("Sheet1").Range("B7:B10")
'Only execute the code if there is something in the cell.
If Cell.Value <> "" Then
'Is there a workbook open with that name?
For Each wrkBk In Workbooks
'Not seen this before, but a file name saved in One-Drive with a comma in
'seems to translate the comma into "^J".
'If this is the case for you use UCase(Replace(Cell.Value, ",", "^J")) rather than just UCase(Cell.Value) below.
'UCASE converts text to upper-case.
If UCase(wrkBk.Name) = UCase(Cell.Value) & ".XLSX" Then
'If the workbook is found then exit the loop.
Exit For
End If
'Set the wrkBk back to nothing so the next code block works if the target workbook isn't open.
Set wrkBk = Nothing
Next wrkBk
'If wrkBk is nothing at this point then the file needs to be opened.
'I've hard-coded the file path, but this could be picked up from a cell.
If wrkBk Is Nothing Then
Set wrkBk = Workbooks.Open("C:\Users\Darren\Documents\Projects\" & Cell.Value & ".xlsx")
End If
'The Target file should now be open and a reference to it held in the wrkBk variable.
With wrkBk.Worksheets("Sheet1")
With .Range("A3:A25")
Dim MonthSearch As Range
Set MonthSearch = .Find(What:=OffMonth, _
After:=.Cells(1, 1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not MonthSearch Is Nothing Then
'The month has been found, so now to find the day.
With wrkBk.Worksheets("Sheet1").Rows(MonthSearch.Row)
Dim DaySearch As Range
Set DaySearch = .Find(What:=OffDay, _
After:=.Cells(1, 1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not DaySearch Is Nothing Then
'Add S to the cell below the day.
DaySearch.Offset(1) = "S"
Else
'Raise a custom error as the day hasn't been found.
Err.Raise vbObjectError + 1001, , "Day not found"
End If
End With
Else
'The month hasn't been found, so best throw an error.
Err.Raise vbObjectError + 1000, , "Month not found."
End If
End With
End With
End If
Next Cell
End Sub
If you want the code to run as soon as you change the name in column B use this code and add it to the worksheet module.
Option Explicit 'This line needs to go at the very top of the module.
Private Sub Worksheet_Change(ByVal Target As Range)
'The code will only fire if you change a value in range B7:B10 and you're only changing a single cell.
If Not Intersect(Target, Range("B7:B10")) Is Nothing And Target.Cells.Count = 1 Then
Dim OffDate As Date
OffDate = ThisWorkbook.Worksheets("Sheet1").Range("B1")
Dim OffMonth As String
OffMonth = Format(OffDate, "mmmm")
Dim OffDay As Long
OffDay = Day(OffDate)
'Look at each cell in the range in turn.
Dim wrkBk As Workbook
'Only execute the code if there is something in the cell.
If Target.Value <> "" Then
'Is there a workbook open with that name?
For Each wrkBk In Workbooks
'Not seen this before, but a file name saved in One-Drive with a comma in
'seems to translate the comma into "^J".
'If this is the case for you use UCase(Replace(Cell.Value, ",", "^J")) rather than just UCase(Cell.Value) below.
'UCASE converts text to upper-case.
If UCase(wrkBk.Name) = UCase(Target.Value) & ".XLSX" Then
'If the workbook is found then exit the loop.
Exit For
End If
'Set the wrkBk back to nothing so the next code block works if the target workbook isn't open.
Set wrkBk = Nothing
Next wrkBk
'If wrkBk is nothing at this point then the file needs to be opened.
'I've hard-coded the file path, but this could be picked up from a cell.
If wrkBk Is Nothing Then
Set wrkBk = Workbooks.Open("C:\Users\Darren\Documents\Projects\" & Target.Value & ".xlsx")
End If
'The Target file should now be open and a reference to it held in the wrkBk variable.
With wrkBk.Worksheets("Sheet1")
With .Range("A3:A25")
Dim MonthSearch As Range
Set MonthSearch = .Find(What:=OffMonth, _
After:=.Cells(1, 1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not MonthSearch Is Nothing Then
'The month has been found, so now to find the day.
With wrkBk.Worksheets("Sheet1").Rows(MonthSearch.Row)
Dim DaySearch As Range
Set DaySearch = .Find(What:=OffDay, _
After:=.Cells(1, 1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not DaySearch Is Nothing Then
'Add S to the cell below the day.
DaySearch.Offset(1) = "S"
Else
'Raise a custom error.
Err.Raise vbObjectError + 1001, , "Day not found"
End If
End With
Else
'The month hasn't been found, so best throw an error.
Err.Raise vbObjectError + 1000, , "Month not found."
End If
End With
End With
End If
End If
End Sub
Further reading:
FIND
WITH Statement
For Each...Next Statement
Raise method
