VBA to collate information from 3 workbooks into one Ranked Table using specific filters

[zainycmt]

Hi All,

I am a massive noobie when it comes to VBA so I am hoping you can assist me in this fun little project.

To give you an Overview basically I need to create a Table which Ranks Areas of the business based on information from 3 separate spreadsheets.

The table I wish to create will look something like this - Called "U1SE - Matrix.xls":

U1SE - Matrix.xlsx

My Filter data / Macro button will run from "U1SE - Branches.xlsm"

U1SE - Branches.xlsm

Then I need to pull data from these 3 files to create the ranked table "U1SE - Matrix.xls"

TEST RES report.xls
TEST DOWN (With Date Parameters).xls
TEST CF 13-07-2015.xlsx

TEST RES report.xls - Filter / VBA required
TEST RES report.xls

In the 1st file ("TEST RES report.xls") I need to filter the data using the filter/macro workbook "U1SE - Branches.xlsm". Then the column I need to filter is "E" and the data that needs to be pulled into the ranked table is Column "F".

Column "F" should then be placed into the created table "U1SE - Matrix.xls" (the top image) in Column "C" I then need to put a ranked number in Column "D" with the highest % number in Column "C" is Ranked the lowest. Obviously if there are two numbers the same then they should both get the same ranked number.


TEST DOWN (With Date Parameters).xls - Filter / VBA required
TEST DOWN (With Date Parameters).xls

In the 2nd file ("TEST DOWN (With Date Parameters).xls") I need to filter the data using the filter/macro workbook "U1SE - Branches.xlsm". Then the column I need to filter is "A" and the data that needs to be pulled into the ranked table will be a Count of how many duplicate occurrences there are in just column "A".

The duplicate occurrence count from Column "A" should then be placed into the created table "U1SE - Matrix.xls" (the top image) in Column "E" I then need to put a ranked number in Column "F" with the lowest number in Column "E" is Ranked the lowest. Obviously if there are two numbers the same then they should both get the same ranked number.



TEST CF 13-07-2015.xls - Filter / VBA required
TEST CF 13-07-2015.xlsx

In the 3rd file ("TEST CF 13-07-2015.xls") I need to filter the data using the filter/macro workbook "U1SE - Branches.xlsm". Then the data I need to filter is in the worksheet "Branch" and column "A". The data that needs to be pulled into the ranked table will be from column "G"

Column "G" should then be placed into the created table "U1SE - Matrix.xls" (the top image) in Column "G" I then need to put a ranked number in Column "H" with the highest number in Column "G" is Ranked the lowest. Obviously if there are two numbers the same then they should both get the same ranked number.


So the file table that is created should look like the top image. I will be grateful for all help. I do have some VBA code in my filter/macro sheet but I can't get anything ive searched for to work.