macro to insert row in multiple sheets

Question

Hello, I have a workbook that tracks attendance.  I have created a workbook with multiple sheets to collect data for days, weeks, months, etc....  I need to be able to add new member's  names to a list on "Worksheet A" and then have it automatically add the same row on "Worksheet C" and "Worksheet  F" so that all of the data associated with the names above and below the new row stay connected to the correct member. Please note that my list is alphabetical, so I need to be able to insert a new row into the middle of a list ON EACH SHEET. 

I work for a non-profit org and most of the people who enter data are NOT computer savvy, so i would like to create a button or something where I can type in the row # that needs to be inserted - Then have the text (member name) automatically fill in one cell, and the formula's associated with the rows above and below are copied to the new row.

EXAMPLE:

Austin, Jane                   |             =SUM(C3:J3)        |        ='Worksheet C MONDAY'!G3

Baldwin, James            |             =SUM(C4:J4)        |        ='Worksheet C MONDAY'!G4

Capote, Truman            |             =SUM(C5:J5)        |        ='Worksheet C MONDAY'!G5

Eliot, George                  |             =SUM(C6:J6)        |        ='Worksheet C MONDAY'!G6

and I would like to add "Darwin, Charles" to the list between "Capote, Truman" and "Eliot, George" -- AND have it populate across multiple sheets.

FIRST -- is this possible, and if so,

SECOND -- can I create a simple user interface into a worksheet for "non-tech" data entry staff?

THANKS   --  Toby

Answer 1

Hi,

Put a button on any of the workshets where you want this then attach this code. At the bottom of the code the underlined bit is the name of the worksheets that you want the data added to. Edit as required

Private Sub CommandButton1_Click()

Dim Response As String, NewName As String

Dim ws As Worksheet

Response = MsgBox("Have you selected the row below where you want to insert data?", vbYesNo)

If Response = vbNo Then Exit Sub

NewName = InputBox("Enter new name in the format 'Secondname, Forename")

If NewName = vbNullString Then

    MsgBox "Sorry I didn't get that name, re-try from start"

    Exit Sub

End If

r = ActiveCell.Row

'note these must be the names of the sheets as they appear

'on the worksheet tab

For Each ws In Sheets(Array**("Worksheet A", "Worksheet B", "Worksheet C"))**With ws

    .Rows(r).EntireRow.Insert

    .Cells(r, 1) = NewName

    .Cells(r - 1, 2).Resize(2, 256).FillDown

End With

Next

End Sub

Answer 2

Hi,

you can do that you want

using a code.

change names:

WorksheetA, C, F  as needed.

(before you run the code, make a copy)

step1

1. press ALT+F11 to open VB editor.
2. Insert > module and paste the code below on the right

(from Sub abc()......End Sub)

3. Close VB editor.

Sub abc()

'20 Dec 2011

Dim ws As Worksheet, v As Variant

Dim X As Long, r As Long, c As Long

Dim rng As Range

v = Array("WorksheetA", "WorksheetC", "WorksheetF")

Set rng = Selection

r = rng.Row

On Error Resume Next

For X = 0 To UBound(v)

Set ws = Sheets(v(X))

c = ws.Cells(r, Columns.Count).End(xlToLeft).Column

ws.Rows(r).Insert

For Each rr In ws.Range(ws.Cells(r - 1, 1), ws.Cells(r - 1, c)) _

.SpecialCells(xlCellTypeFormulas)

rr.Copy ws.Cells(r, rr.Column)

Next rr

Next X

On Error GoTo 0

End Sub

step 2

To run the macro, press ALT+F8, 

select <abc> from the list and click the run button.

Note

before you run the macro,

select the cell you want to insert new row

Answer 3

Thanks Mike H.     I tried this, but I get the following error message:

Run-Time error '1004':

Application-defined or object-defined error

When I debug, the following line in the code appears highlighted:

     .Cells(r-1, 2). Resize(2, 256).FillDown

I'm not sure where the error is occurring.

Thanks for any help you can give.

Toby

Answer 4

Hi.  Thanks so much for your help.  Unfortunately, I'm getting a

"Compile Error -- Invalid or unqualified reference" error message that highlights

the    .SpecialCells    portion of the code.

I highlighted a row before I ran the macro.  Should I have inserted a blank row first?

Thanks  Toby

Answer 5

The simplest approach is to use a macro that:

1. Asks for the new name
2. Adds the new row at the bottom of the master sheet
3. Adds the formulae to the new row
4. Re-sorts the rows to put the new row in alphabetic order
5. Copies the data to the other sheets

To complete this task we need to know the details of how your sheets are layed out.