Add a Row to Multiple Worksheets Simultaneously

[Jamie]

I am working in Excel 2002.

I have 12 worksheets, January to December, contained in the same workbook.
Each month tracks errors made within that month (dollar errors, procedure
errors, etc.) for each employee. I have the following macro, AddRow,
attached to a button, Insert a Row. This button is on each worksheet. If a
new employee needs to be added, the user clicks on the Insert Row button.
The macro copies all the formulas in the row above it into a new blank row.

Sub AddRow()
Dim Msg, Style, Title, Response
Dim rngAdd As Range
Set rngAdd = ActiveCell

Msg = "Do you want to insert a row?" 'Define message.
Style = vbYesNo + vbDefaultButton2 'Define buttons.
Title = "Insert a Row" 'Define MsgBox title.

Response = MsgBox(Msg, Style, Title)

If Response = vbYes Then 'User chooses Yes.

ActiveSheet.Unprotect

rngAdd.Offset(0, 0).EntireRow.Insert 'Add a row "above" the current
position
rngAdd.EntireRow.Copy 'Copy the row you just "moved down"

rngAdd.Offset(-1, 0).EntireRow.PasteSpecial xlPasteAll
'Paste "move" the old data "up" into newly created row

'This prevents the Run-time error '1004': No cells were found,
'from appearing.
On Error Resume Next
Application.EnableCancelKey = xlErrorHandler

rngAdd.EntireRow.SpecialCells(xlCellTypeConstants).ClearContents
'Clear old row so that it "appears" to be a "new" row

ActiveSheet.Protect
End If
rngAdd.Offset(0, 0).Select
End Sub

This works great, however what I would like to happen now is if the user
clicks the Insert a Row button, no matter what month (worksheet) they are in,
a new row is added to each worksheet simultaneously. The new row would need
to contain the formulas just like the ones above it.

For instance, currently there are 12 employees, lets say in March a new
employee is hired. I would like to click on the Insert a Row button, in the
March worksheet, and have a row added to each worksheet (January to
December). Because now there will be 13 employees.

To recap I would like the macro, AddRow, above to do what it does now but
also add a row to each worksheet (January to December).

I hope this make sense. Thanks for any help you can provide.

 

[RA]

I'm not savy enough to have the answer but I will add what I know:

I created three spreadsheets and an insert macro, when I selected all
spreadsheets and ran the macro, it inserted the line in all sheets. If it is
possble to have the selection of all sheets in the macro, it should work.

 

[Don Guillett]

try this to insert a row at the active cell and copy your defined name range
myformulas to the row above.

Sub copytoall()
Dim myaddress As String
myaddress = ActiveCell.Address
For Each ws In Worksheets
With ws
..Select
..Range(myaddress).Insert
Range("myformulas").Copy _
ActiveSheet.Range(myaddress)
End With
Next ws