VBA Express : Excel - Insert Row into All Worksheets

Excel

Insert Row into All Worksheets

Ease of Use

Intermediate

Version tested with

2000

Submitted by:

Brandtrock

Description:

This code inserts a row into all worksheets of a workbook at the same location in each worksheet. The user inputs the row position to insert.

Discussion:

Worksheets that link to a master sheet sometimes need a row inserted for new data to be added. This code inserts a line into each sheet of the workbook, preserving the relative references in the formulas. A simple copy and paste routine can be added to copy formulas down to the new row if required. Use this routine on a Before_Double_Click event to allow users the ability to add lines simply by double clicking any cell and supplying the row number in the prompt box.

Code:

instructions for use

Option Explicit Sub InsertRowAllSheets() ' Thanks to firefytr for the code that has been adapted into this routine Dim cs As String cs = ActiveSheet.Name Dim y As Integer y = Application.InputBox("Enter the row number you wish to add", _ Type:=1) 'enter 16 to insert a new row 16, the old row _ will become 17 And all other rows push down 1 row As well. If MsgBox("Are you sure you wish to insert at row " & y & " for ALL sheets?", _ vbYesNo, "Insert row on ALL Sheets") = vbNo Then Exit Sub Application.ScreenUpdating = False Dim r As Range Dim ws As Worksheet ' On Error Resume Next 'Error handler For Each ws In ThisWorkbook.Worksheets ws.Activate Set r = ActiveSheet.Range("A" & y) If y < 7 Then Goto circumv 'Not to insert in Headers Range("A" & y).EntireRow.Insert ' code can be inserted here to copy formulas for some or all sheets in the workbook circumv: Next ws Sheets(cs).Activate Application.ScreenUpdating = True End Sub

How to use:

Copy the above code.
Open any workbook.
Press Alt + F11 to open the Visual Basic Editor (VBE).
From the Menu, choose Insert-Module.
Paste the code into the right-hand code window.
Close the VBE, save the file if desired.

Test the code:

Tools-Macro-Macros, and double-click InsertRowAllSheets

Sample File:

InsertRows.zip 8.74KB

Approved by mdmackillop

This entry has been viewed 107 times.