Adding a Row Simultaneously in Multiple Worksheets

J

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

JLGWhiz

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?
 
J

Jamie

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top