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


Sub Auto_Open()
Call formuladrop
Application.DisplayAlerts = False
End Sub
Sub formuladrop()
'
' formuladrop Macro
'

'
    Workbooks.Open Filename:= _
        "S:\1. Original.xls"
    ActiveSheet.Range("$A$1:$CH$21619").AutoFilter Field:=7, Criteria1:="1"
    Range("J2").Select
    Selection.AutoFill Destination:=Range("J2:CH2"), Type:=xlFillDefault
    Range("J2:CH2").Select
    Selection.FillDown
    Range("A1").Select
    ActiveSheet.Range("$A$1:$CH$21619").AutoFilter Field:=7, Criteria1:="4"
    Range("I5").Select
    Selection.AutoFill Destination:=Range("I5:CH5"), Type:=xlFillDefault
    Range("I5:CH5").Select
    Selection.FillDown
    Range("A1").Select
    Selection.AutoFilter
    Range("I2").Select
    ActiveWindow.FreezePanes = True
    Application.DisplayAlerts = False
    ActiveWorkbook.Save
    ActiveWindow.Close
    ActiveWindow.Close
    Application.Quit
End Sub
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