Adding a Row Simultaneously in Multiple Worksheets

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 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 in addition to creating a new row and copying the
formulas as it does now in the current worksheet, I would like a row added to
each worksheet (January to December) simultaneously.

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).

Thanks for any help you can provide.
 
Jamie, my old brain is not as spry as it used to be. Let me see if I really
understand what you want to do, put in my words.

You want to copy a row of data, insert a new row, clear the contents of the
old row and save the results for twelve worksheets simultaneously.

Do I have it correct?
 
Hi JLGWhiz, the coding that I provide I have to go to each worksheet and
click the button to add a new row. What I would like to do is, if in March I
get a new employee I would like to click on the Insert a Row button and not
only does a new blank row with the formulas appear in the March worksheet,
but also in April to February.

So after entering the new employee info for March I can click on the April
worksheet and there is a blank row present for me to enter April info for
this person.

I hope this helps.
 
Back
Top