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.
--
Jamie
"JLGWhiz" wrote:
> 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?
>
> "Jamie" wrote:
>
> > 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
|
Similar Threads
