I have written a macro that filters a range of data, then subtotals a
row of sales figures in that range using the =SUBTOTAL function. A
new set of sales figures comes out every week. But I am unable to
apply the same macro to the new sales figures because the number of
rows in the worksheet changes from week to week. The range in the
subtotal formula stays static while the numbers of rows is increased
or decreased each week. This causes the subtotal formula to only pick
up the range of data I originally asked for when I wrote the macro.

This is a copy of the macro…

Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.AutoFilter
Rows("2:2").Select
ActiveWindow.FreezePanes = True
Columns("E:E").Select
Selection.Insert Shift:=xlToRight
Selection.ColumnWidth = 4
Range("D2").Select
Selection.End(xlDown).Select
Selection.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "D"
Selection.Copy
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Range("E2").Select
Selection.End(xlDown).Select

Selection.Offset(2, 2).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-327]C:R[-2]C)"

So if I run the macro the next week and the sheet has more or less
than 327 rows the macro only subtotals from E2 to E327.

Does anyone know how I can make the range that I subtotal relative to
where the offset starts back to the top of the column?

Thanks in advance,

Steve M.