VBA: Insert user defined amount of copies of a row

Question

I have been dealing with a huge problem for a couple of weeks now and I cant fix it no matter what. Whatever I do, it works for a while until something new pop.

Objective: in a specific column of a table, allow users to type down an integer and depending on that amount insert x rows in the table (all of them being exact copies of the original row)

I have the code below I got from another site and modified it:

I got a WorksheetChange event that triggers a macro in another module.

The code below works only in non filtered tables, but once the user filters a table and the macro runs it doesnt work. The rows are inserted and are visible on whatever filter the user has, but the copy from the original row does not. I get no error, is just that the rows are inserted empty.

How can I insert whatever amount of rows the user specified in a filtered table (without taking out the filters) and copy/paste everything form the original row?

Sub InsertRows(ByVal splitVal As Integer, ByVal keyCells As Range, ws As Worksheet)

    On Error GoTo ErrorHandler
    PW
    ws.Unprotect Password
    ws.DisplayPageBreaks = False

    With keyCells
        .Offset(1).Resize(splitVal).EntireRow.Insert
        .EntireRow.Copy .Offset(1, 0).Resize(splitVal).EntireRow
    End With

ExitHandler:
    ws.Protect Password:=Password, DrawingObjects:=True, Contents:=True, Scenarios:=False _
    , AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True, AllowFormattingRows:=True, AllowFormattingColumns:=True, AllowFormattingCells:=True
    Exit Sub

ErrorHandler:
    WBNorm
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Insert_Rows, line " & Erl & "."
    GoTo ExitHandler

End Sub

Answer 1

Have you tried doing something along the lines of:

1. Turn off screen updates
2. remove the filter
3. add the rows
4. re-enable the filter

There is an answered question on how you can save filter criteria and reapply them later on: In Excel VBA, how do I save / restore a user-defined filter?