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