Macro: Filter spreadsheet, then apply formula to results

Version: Excel 2013

Hey,

I've got a spreadsheet that I wish to filter a column on. The first line of the filter's result has a formula in one cell, and I would like to apply that formula to the rest of the filter results. Then, I would like to apply a different filter, and do the same.

I've attached the spreadsheet as file '1. Original.xls'. The filter already exists when the file is created. Here's the process I follow manually and want to turn into a macro:

1. Filter column G by value '1'.
2. Select Cell J2 (formula is in this cell).
3. Drag this formula to the end of Row 2, CH2
4. Drop down the highlighted cells, applying the formula to all rows that have value '1' in column G.
5. Change column G's filter to value '4'.
6. Select Cell I5 (formula is in this cell).
7. Drag this formula to the end of Row 5, CH5
8. Drop down the highlighted cells, applying the formula to all rows that have the value '4' in column G.
9. Remove the filter
10. Select Cell I2 and freeze panes.
11. Save.

Attached file '2. Manual.xls' is the result I get when I do things manually, and exactly what I need. Attached file '3. Macro.xls' is what I get when I run a recorded macro. The macro code is below. What it looks like is happening is the 'drop down' command is dropping down the headers row (1) instead of the formula rows (2, 5). You'll also note from the code (slightly amended from the recording) is that the macro runs automatically when the XLSM is opened, and closes the file when complete.

Wrong dropdown.jpg
Working manually.jpg

Please Login or Register  to view this content.

Files (Google Drive, too big to upload here):

1. Original -- https://drive.google.com/open?id=0By...dDA&authuser=0
2. Manual -- https://drive.google.com/open?id=0By...VmM&authuser=0
3. Macro -- https://drive.google.com/open?id=0By...bnc&authuser=0

Any pointers would really help!

Thanks,

Ruairidh