|
|
|
|
|
|
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()
Dim cs As String
cs = ActiveSheet.Name
Dim y As Integer
y = Application.InputBox("Enter the row number you wish to add", _
Type:=1)
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
For Each ws In ThisWorkbook.Worksheets
ws.Activate
Set r = ActiveSheet.Range("A" & y)
If y < 7 Then GoTo circumv
Range("A" & y).EntireRow.Insert
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 189 times.
|
|