VBA Express : Excel - Copying & Inserting Rows

Excel

Copying & Inserting Rows

Ease of Use

Easy

Version tested with

2010

Submitted by:

dazwm

Description:

Run this code and it will copy and insert the row highlighted, directly below the amount of times stated by the user in a input box

Discussion:

I use this code when I want to copy and insert rows. This saves time by not having to select the row, right-click and copy, then highlight the row where you want to insert this row etc etc. You can insert more than the one time by the amount you require by entering it in the input box.

Code:

instructions for use

			Sub CommandButton1_Click() 
    Dim NextRow As Long 
    Dim NrOfCopies As Long 
    Dim i As Long 
     
    Const NrOfCopiesDefault = 1 
    Const NrOfCopiesMaximum = 9 
     
    Do 
         
        On Error Resume Next 
        NrOfCopies = Application.InputBox(prompt:="How Many Copies Do You Want To Copy & Insert?", _ 
        Title:="# COPIES", Default:=NrOfCopiesDefault, Type:=1) 
        On Error GoTo 0 
         
        If NrOfCopies = 0 Then 
            MsgBox "No copies made.", vbInformation, "CANCELLED" 
            Exit Sub 
        ElseIf NrOfCopies > NrOfCopiesMaximum Then MsgBox "Please Enter Number Of Copies Between 1 and " & NrOfCopiesMaximum, 48, "ERROR" 
        End If 
         
    Loop While NrOfCopies < 1 Or NrOfCopies > NrOfCopiesMaximum 
     
    With Selection 
        NextRow = .Row + .Rows.Count 
        Rows(NextRow & ":" & NextRow + .Rows.Count * (NrOfCopies) - 1).Insert Shift:=xlDown 
        .EntireRow.Copy Rows(NextRow & ":" & NextRow + .Rows.Count * (NrOfCopies) - 1) 
        .Resize(.Rows.Count * (NrOfCopies + 1)).Sort key1:=.Cells(1, 1) 
    End With 
     
End Sub

How to use:

Save a copy of your original file for safekeeping.
Open the workbook and hit Alt+F11 to view the Visual Basic Editor (VBE).
Choose your workbook at left and hit Insert-Module.
Paste the code (from above) into the code window that appears at right.
Hit the Save diskette.
Close the VBE

Test the code:

Highlight the row you wish to copy.
Hit View>Macros>View Macros and double-click CommandButton1_Click.
Enter amount of rows you wish to copy and insert in Input box.

Sample File:

Example.zip 14.82KB

Approved by Jacob Hilderbrand

This entry has been viewed 350 times.