I'm a complete VBA newbie. I've got a worksheet where I want to allow the user to insert rows 101:106 above the active cell that the user selects. Using the below code, the macro pastes the rows below row 106, and not at the row the user selects....

What's the best way to fix this?

Sub InsertRowPlanning()
'
' InsertRowPlanning Macro
'
    ActiveSheet.Unprotect Password:=PWORD
    Rows("101:106").Select
    Selection.Copy
    ActiveCell.Select
    Selection.Insert Shift:=xlDown
    ActiveCell.EntireRow.Select
    Application.CutCopyMode = False
    ActiveSheet.Protect Password:=PWORD
End Sub

Thanks in advance!

Sunil

Either change this to the row where you want to paste

for example

Rows("6:6").Insert Shift:=xlDown ' Where 6 is the row numb for example

or select the row manually where you want the data to be pasted and then run the macro..

Hi,

I am also looking for something similar - to insert rows and copy down values.

Thus far I have found this from mvps.org - from a previous post. It inserts/copies only the first selected row. Can you help me do this for all the rows in my sheet.

Sub InsertRowsAndFillFormulas(Optional vRows As Long = 0)

   Dim x as long
   ActiveCell.EntireRow.Select 

   If vRows = 0 Then
    vRows = Application.InputBox(prompt:= _
      "How many rows do you want to add?", Title:="Add Rows", _
      Default:=1, Type:=1) 'Default for 1 row, type 1 is number
    If vRows = False Then Exit Sub
   End If

    
   Dim sht As Worksheet, shts() As String, i As Integer
   ReDim shts(1 To Worksheets.Application.ActiveWorkbook. _
       Windows(1).SelectedSheets.Count)
   i = 0
   For Each sht In _
       Application.ActiveWorkbook.Windows(1).SelectedSheets
    Sheets(sht.Name).Select
    i = i + 1
    shts(i) = sht.Name

    x = Sheets(sht.name).UsedRange.Rows.Count 

    Selection.Resize(rowsize:=2).Rows(2).EntireRow. _
     Resize(rowsize:=vRows).Insert Shift:=xlDown

    Selection.AutoFill Selection.Resize( _
     rowsize:=vRows + 1), xlFillcopy

    On Error Resume Next    
    Selection.Offset(1).Resize(vRows).EntireRow. _
     SpecialCells(xlConstants)

   Next sht
   Worksheets(shts).Select
End Sub