# Using VBA in place of an INDEX and MATCH statement.

#### 4rcanine

##### New Member
Howdy Guys,

I've gotten myself stuck trying to improve a workbook I use while at work and am in dire need of help.

The workbook contains four worksheets, the first being a master list/table of information. The next three worksheets are the departments at my work and show the same information as the master however each worksheet only shows that departments information.
Previously the data was copied into each worksheet manually and I am trying to automate the process.

Initially I achieved this using the following INDEX and MATCH statement:
=IF(ISERROR(INDEX(MASTER!\$A\$2:\$P\$499,SMALL(IF((MASTER!\$D\$2:\$D\$499="1"),MATCH(ROW(MASTER!\$B\$2:\$B\$499),ROW(MASTER!\$B\$2:\$B\$499))),ROW(MASTER!G49)),COLUMN(MASTER!G49)))," ",INDEX(MASTER!\$A\$2:\$P\$499,SMALL(IF((MASTER!\$D\$2:\$D\$499="1"),MATCH(ROW(MASTER!\$B\$2:\$B\$499),ROW(MASTER!\$B\$2:\$B\$499))),ROW(MASTER!G49)),COLUMN(MASTER!G49)))

This formula is in cells A2:P150 on each department's worksheet. The department name is in column D, which is what it matches. This has worked fine until I've enconuntered the need to copy cell formatting along with the information (mainly cell shading/fill).

Is there a way this can be done within VBA, say each time a worksheet is opened?

Cheers!

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
I would record a macro in which you:
- Clear the dept worksheets
- For each dept:
- Filter the master table on the Dept
- Copy the visible rows to the associated dept sheet

Replies
3
Views
151
Replies
1
Views
578
Replies
6
Views
809
Replies
9
Views
479
Replies
0
Views
380

1,203,327
Messages
6,054,751
Members
444,748
Latest member
knowak87

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back