AutoFill formatting after using VBA to insert rows in multiple sheets

[Diwha]

I am using the following code to insert rows in two sheets at the same time. However I also want to copy down the formulas from the row above those inserted on the second sheet after the additional rows have been added. Any help would be much appreciated,

Sub insertRowsSheets()

'Disable Excel properties before macro runs
With Application
.Calculation = xlCalculationManual
.EnableEvents = False
.ScreenUpdating = False
End With

'Declare obiect variables
Dim ws As Worksheet, iCountRows As Integer
Dim activeSheet As Worksheet, activeRow As Long
Dim startSheet As String

'State activeRow
activeRow = ActiveCell.Row

'Save initial active sheet selection
startSheet = ThisWorkbook.activeSheet.Name

'Trigger input message to appear in terms of how many rows to insert
iCountRows = Application.InputBox(Prompt:="How many rows do you want to insert, starting with row" & activeRow & "?", Type:=1)

'Error handling end the macro it a zero, negative integer or non-integer value is entered
If iCountRows = False Or iCountRows <= 0 Then End

'Loop through the worksheets in Active Workbook
For Each ws In ActiveWorkbook.Sheets

ws.Activate
Rows(activeRow & ":" & activeRow + iCountRows - 1).Insert Shift:=xlDown

Next ws

'Move cursor back to intial worksheet
Worksheets(startSheet).Select
Range("A1").Select

'Re-enable Excel properties once macro is complete
With Application
.Calculation = xlCalculationAutomatic
.EnableEvents = True
.ScreenUpdating = True

End With


End Sub

 

[sivior]

i use this code i think it should workfor your needs aswell just change the ranges

Sub AddRows()
    
    Const CopyRow As Long = 42
    
    Dim xCount As Variant
    Do
        xCount = Application.InputBox("Aantal rijen", "VERKOPEN DIE HANDEL", , , , , , 1)
        If TypeName(xCount) = "Boolean" Then
            MsgBox "You canceled.", vbExclamation
            Exit Sub
        End If
        If xCount < 1 Then
            MsgBox "the entered number of rows is to small, please enter again", vbCritical, "testing"
        Else
            Exit Do
        End If
    Loop
    
    Dim ash As Object: Set ash = activesheet
    Dim wb As Workbook: Set wb = ash.Parent
    
    Application.ScreenUpdating = False
    
    Dim ws As Worksheet
    Dim wsCount As Long
    
    For Each ws In wb.Worksheets
        wsCount = wsCount + 1
        With ws.Rows(CopyRow)
            .Copy
            .Offset(-1).Resize(xCount).Insert xlShiftDown, xlFormatFromLeftOrAbove
        End With
    Next ws
    
    Dim MsgString As String
    MsgString = "Worksheets processed: " & wsCount
    
    If wsCount > 0 Then
        Application.CutCopyMode = False
        ash.Select
        MsgString = MsgString & vbLf & "Rows inserted: " & xCount
    End If
    
    Application.ScreenUpdating = True
    
    MsgBox MsgString, vbInformation
    
End Sub