Hi guys,

I've been trying for ages to get together some code that will allow me to insert a specified number of rows into excel using vb and at the same time copy down any text/date/numbers from the row above into these new inserted rows.

Thus far I have found this from mvps.org -

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), xlFillDefault

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

   Next sht
   Worksheets(shts).Select
End Sub

This works fine as far as it inserts the number of rows I want and copies everything down, but any cell with a number in it gets increased by 1 for every row copied down. I have experimented with altering the SpecialCells(xlConstants) part of the code but with no success.

So I think I basically need the code to copy down and paste special values!

Anybodys help on this would be greatly appreciated (especially as copying and pasting hundreds or rows is damaging my hand!)

Thanks in advance

just follow the steps below whenever you have problems with excel vba coding.



1. start recording macro.

2. use the mouse to do the thing you want the code to do.

3. stop macro recording.

4. look at the code macro recording generated for you.

5. learn.

6. replace static values with variable.

7. throw the entire thing in a loop.

8. make a button to run the loop.

9. do something else.



there you have it vba coding the easy way.

I also came across this problem. The code works good for me.

You might need to change

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

to

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

I want to copy down varied number of the rows. The varied number is in my original table, whose variable name is "num". How can I modify the code to copy down the entire table altogether instead of typing in a number one at a time?

Thanks a lot.

This macro only added rows after the first row and copied the contents. How do I make it go all the way down to the last row in my sheet ?

thanks