Click here for important information about Computing.net.

Solved Macro to copy, paste, insert in Excel

Tags:
excel
macros
March 12, 2013 at 09:46:34
Specs: Windows 7
Hello,

I am trying to do the following - I have a data sheet with about 100 rows, and would like to write a macro where it copies a selected row and inserts /pastes directly underneath it a specified number of times.

e.g. user selects row 5 and wants that data to be repeated 17 times underneath it.

Any help you can give would be great,
thanks, Ricky


See More: Macro to copy, paste, insert in Excel

Report •

#1
March 12, 2013 at 13:07:53
✔ Best Answer
Sub InsertRows()
'Make sure user has selected 1 entire row
  If Selection.Cells.Count <> Selection.Resize(1).EntireRow.Cells.Count Then
   MsgBox "Please select 1 entire row, then rerun macro."
   Exit Sub
  End If
'Get number of rows to insert from user
getNum:
   myCopyNum = Application.InputBox("How Many Times Do You Want the Row Copied", Type:=1, Default:=0)
'Make sure sneaky users can only enter integer values
     If Int(myCopyNum) <> myCopyNum Then
        MsgBox "Enter Integer Value or Click Cancel"
        GoTo getNum
     End If
'Quit if Cancelled
     If myCopyNum = False Then Exit Sub 'Copy and Insert Rows
        Selection.Copy
         Rows(Selection.Row + 1 & ":" & Selection.Row + myCopyNum).Insert Shift:=xlDown
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
March 14, 2013 at 07:34:29
DerbyDad03 -- thanks so much, this code works perfectly for what I need. you have saved me a lot of time and made my life considerably easier.

Thanks again.
Ricky


Report •
Start a discussion
Related Solutions
See More ↓


Ask Question