VBA Alternative to array index match

[Helgard25]

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

[PCI]

"Is there a VBA code I can use to perform a similar task?"
Yes certainly.
What is the reason you need to turn to macro?
Can you send a short sample of your data to help to prepare and debug the code.

[Helgard25]

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.

[PCI]

Do I'm wrong but there is no data in the file ...!

[Helgard25]

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

[PCI]

Can you not use "dummy' data? yes and please prepare your file with such data and pay attention to have the same data type: Date, String, Currency etc...

[Helgard25]

Good day,

Please find updated file

[jindon]

This should do

Please Login or Register  to view this content.