Track expired items in separate sheet

Hi,
New member here, just starting to figure out the wonders of excel and hoping someone can help me. I am trying to track medications stocked on our ambulances. We have 7 ambulances each is a separate sheet. The list of medication names is column A, and the expiration dates are listed in columns B-I (as we carry up to 8 vials of any given item). I already have the conditional formatting set up to highlight different colors 30 days prior, 14 days prior etc. in each sheet, I would like to create a separate sheet that will list items expiring in next 14 days and their location (basically, the sheet name). Not sure how to go about doing this. I am also unfamiliar with any other kinds of 'code' other than formulas that are entered into the excel program.

I am attaching the sheet I have set up so far,
Thanks in advance for your help

Expiration Dates.xlsx

So which columns do you want displayed in the new sheet? Do you want it in the same format as your other sheets?

You do not require the dates to be displayed?

Try this code

Option Explicit

Sub expiry_dates()
Dim i As Long, lrow As Long, a As Long, j As Long

Application.ScreenUpdating = False

Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Report"
For i = 1 To Worksheets.Count
    With Worksheets(i)
        If .Name <> "Report" And .Name <> "Sheet1" Then
            lrow = .Range("A" & .Rows.Count).End(xlUp).Row
            For a = 1 To lrow
                For j = 2 To 9
                    If .Cells(a, j).Value - Date <= 14 And .Cells(a, j).Value <> "" Then
                        Worksheets("Report").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = .Cells(a, 1).Value
                        Worksheets("Report").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Value = .Name
                    End If
                Next j
            Next a
        End If
    End With
Next i

Application.ScreenUpdating = True

End Sub

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button

The output gives multiple rows for the same medicine if it finds more than 1 vial having the same date <= 14 days. Let me know if you need a code to remove the duplicate values.

Updated code to avoid duplications-

Option Explicit

Sub expiry_dates()
Dim i As Long, lrow As Long, a As Long, j As Long

Application.ScreenUpdating = False

Worksheets("Report").Delete
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Report"
For i = 1 To Worksheets.Count
    With Worksheets(i)
        If .Name <> "Report" And .Name <> "Sheet1" Then
            lrow = .Range("A" & .Rows.Count).End(xlUp).Row
            For a = 1 To lrow
                For j = 2 To 9
                    If .Cells(a, j).Value - Date <= 14 And .Cells(a, j).Value <> "" Then
                        Worksheets("Report").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = .Cells(a, 1).Value
                        Worksheets("Report").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Value = .Name
                    End If
                Next j
            Next a
        End If
    End With
Next i

Application.ScreenUpdating = True

End Sub

1- should it refresh every time something is changed or entered? Right now when I enter a date on one of the other sheets, it's not showing up in 'report'. Am I not doing something correctly?

Each time something is entered, you will need to re-run the macro. However, that can result in duplications. I will provide you the updated code which will clear the output sheet and then give you the details afresh without duplications.

2- Is it possible to make the name or location show up in color so I can differentiate between 14 days and 30 days?

The code is only bringing out those which are expiring in 14 days, where do the 30 days entries feature?