Filter, Cell Reference formula and copy formula on filter data
 

Hi There,

I'm having a little problem here

This is what i want to accomplish:

1) filter on Column D that equal Maintenance
2) in column J will have a formula that applies the USD amount in Column I
3) copy the formula down for every row that matches Maintenance



The problem i'm having is that Each month the maintenance will be in
different rows. Alsos the range will change depending the criteria

I created this Macro below:
Sub Applying_Maintenance_Amount_chargeBack()
'
' Applying_Maintenance_Amount_chargeBack Macro
'

'
Sheets("SwitchBoard").Select
Range("A3").Select
Sheets("Chargeback Info for Qtr").Select
ActiveWindow.SmallScroll ToRight:=2
ActiveSheet.Range("$A$7:$S$992").AutoFilter Field:=4, Criteria1:= _
"Maintenance"
Range("J16").Select
ActiveCell.FormulaR1C1 = "=+RC[-1]"
Range("J16").Select
Selection.Copy
Range("I17:J17").Select
Range("J17").Activate
Range(Selection, Selection.End(xlDown)).Select
Range("J17:J992").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A1").Select
ActiveSheet.Range("$A$7:$S$992").AutoFilter Field:=4
End Sub


I hope someone can give me some guidance on this or give me a better
solution to this

thanks
Keith