VBA Alternative to array index match

Good day,

I am currently using a Array index function to return multiple values based on data entered into cells D2-D10000.

My workbook is divided into 7 sheets. A main data sheet whereby I enter data in columns A-Z.

Depending on the letter(a,b,c,d) entered into D2-D10000, the data intered into E-Z in my main data sheet will then be "copied" to my other 6 sheets using an array index function based on the letter entered into D2-D10000 in my main data sheet.

The array formula used in my 6 sheets to return multiple values:
{=IFERROR(INDEX('Combined Cancellations'!$A:$Q,SMALL(IF('Combined Cancellations'!$D$2:$D$9892="b",ROW('Combined Cancellations'!$D$2:$D$9892)),ROW(1:2)),2),"")}

Is there a VBA code I can use to perform a similar task?

Any help would be appreciated

Good day,

Thank you for your response.

I have attached the file. I removed all details due to size constraint. I believe you will get a feel for what I am trying to do.

Basically the main data is entered into the Combined sheet and then distributed to Sheet 1 - Sheet 6 based on data entered into column d of combined sheet.
The main data is pulled from clients sheet.

There is no data o. It is actual info of clients so I cannot publish the data. Can you not use "dummy' data?

Good day,

Please find updated file

This should do

Sub test()
    Dim ws As Worksheet, a, i As Long, ii As Long, dic As Object
    Set dic = CreateObject("Scripting.Dictionary")
    For Each ws In Worksheets
        If ws.Name Like "Sheet*" Then
            a = ws.[a1].CurrentRegion.Value
            For i = 2 To UBound(a, 1)
                If a(i, 2) <> "" Then
                    If Not dic.exists(a(i, 2)) Then
                        Set dic(a(i, 2)) = CreateObject("Scripting.Dictionary")
                        dic(a(i, 2)).CompareMode = 1
                    End If
                    For ii = 3 To UBound(a, 2)
                        dic(a(i, 2))(a(1, ii)) = a(i, ii)
                    Next
                End If
            Next
        End If
    Next
    With Sheets("Combined cancellations").[a1].CurrentRegion
        .Offset(1, 1).ClearContents
        a = .Resize(dic.Count + 1)
        For i = 0 To dic.Count - 1
            a(i + 2, 2) = dic.keys()(i)
            For ii = 3 To UBound(a, 2)
                a(i + 2, ii) = dic.items()(i)(a(1, ii))
            Next
        Next
        .Resize(dic.Count + 1).Value = a
    End With
End Sub