calling a macro and placing data

[B-Man]

Hi, i have a sheet where i have set the filter so i can filter out items

i have found a macro to select the first cell in column A

ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 1).Select

and a macro to select the last cell in column A

Range("A" & Rows.Count).End(xlUp).Select

they might not be great but they seem to work fine

what im after is to somehow run the macro every time i change a filter option and get the value of last cell in column A - (take) the value of the first cell in column A and place that answer into E5

 

[B-Man]

cant seem to edit that post for some reason

i have my formula worked out now but just need to work out how to make it automatically run when i change the filters

Sub BillsFirstLast()
ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 1).Select
Selection.Copy

Range("R2").Select

Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False


Range("A" & Rows.Count).End(xlUp).Select
Selection.Copy

Range("R3").Select

Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False

End Sub

 

[B-Man]

i cant edit... yet it says you may edit your posts in the posting permissions..?

im still having trouble calling the macro
i think its because im trying to call it from a cell that is a formula?

what i want to do is run the macro when a filter changes but i cant find anything about that

then i decided to run the macro based on a cell change but i cant get that working..
the cell has a formula so i think thats where the problem is but i can get it working

id like to run the macro every time the cell D3 changes but cant get anything working?

im doing it in the correct area (sheet code)

the formula in D3 is

=SUBTOTAL(9,D4:D509)