Using VBA in place of an INDEX and MATCH statement.

[4rcanine]

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!

 

[jkpieterse]

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

 

[Andrew Poulsom]

Maybe this will help you:

http://www.mrexcel.com/forum/excel-questions/49839-transfer-data-another-worksheet-name.html