Thread: Inserting rows whilst keeping filter range.

I have found and used the code below which inserts a user defined number of rows into the open worksheet (or multiple worksheets if you select them together). The row i use to click in before adding the extras is always Row 13.

This works really well, with one problem. When I add the extra rows the filter I have set up (range: $A$8:$L$13) does not 'grow' to include the added columns. Instead it only includes information included in the single existing row (Row 13). The ones that appear below it are left out of the filter.

Anyone know how i can solve this? I've tried fiddling with the VBA code but with no luck....The code im using for the macro to add extra rows is as follows....

Sub InsertRows()
'adds desired # of lines below the current line and
' copies the formulas to that/those lines
'added selection of more than one worksheet

Dim vRows As Long
Dim sht As Worksheet, shts() As String, i As Long

' row selection based on active cell --

ActiveCell.EntireRow.Select
vRows = _
Application.InputBox(prompt:= _
"How many students are taking this (shared) module?", Title:="Add Rows", _
Default:=1, Type:=1) 'type 1 is number

If vRows = False Then Exit Sub
'if you just want to add cells and not entire rows
' then delete ".EntireRow" in the following line

ReDim shts(1 To Worksheets.Application.ActiveWorkbook. _
Windows(1).SelectedSheets.Count)
i = 0

'insert rows on grouped worksheets

For Each sht In _
Application.ActiveWorkbook.Windows(1).SelectedSheets
Sheets(sht.Name).Select
i = i + 1
shts(i) = sht.Name

Selection.Resize(rowsize:=2).Rows(2).EntireRow. _
Resize(rowsize:=vRows).Insert Shift:=xlDown
Selection.AutoFill Selection.Resize(rowsize:=vRows + 1), _
xlFillDefault
On Error Resume Next
' to remove the non-formulas
Selection.Offset(1).Resize(vRows).EntireRow. _
SpecialCells(xlConstants).ClearContents
Next sht

'reselect original group
Worksheets(shts).Select

End Sub