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:

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

Test the code:

  1. Tools-Macro-Macros, and double-click InsertRowAllSheets
 

Sample File:

InsertRows.zip 8.74KB 

Approved by mdmackillop


This entry has been viewed 189 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express