Hi All,
In a scheduling worksheet, first and last names are listed in the left
columns, formulas in columns to the right of the names, and then
individual days are added daily in columns to the right of the
formulas. Marks are made in the intersections of days and people.

I would like to have a cmd button or menu to add new people. The
tricky part is that 1 person's info occupies 4 consecutive rows. Each
row contains different formulas that would have to be included in the
copy. It would be best if users could click on any 1 of the 4 rows of
an existing person's record, and then click the cmd to add a new
person's record (4 new rows) either above or below the existing
person's record. Code would have to recognize where one group of rows
starts and ends.

Note that each person's name is put on the top row (cols A and B) of
his or her record, so the presence of text 0 or 1 or 2 or 3 lines
above where the cursor is could be the identifying feature that could
maybe make this happen. I just don't know how. Any help would be
greatly appreciated.

Thanks,
Eric

Eric,

Try the code below. Assumes that last names are in column B, first names in A.

HTH,
Bernie
MS Excel MVP


Sub InsertBlockAboveOrBelow()
Dim myCell As Range
Dim newFName As String
Dim newLName As String

newFName = InputBox("What is the new first name?")
newLName = InputBox("What is the new last name?")

If Cells(ActiveCell.Row, 1).Value = "" Then
Set myCell = Cells(ActiveCell.Row, 1).End(xlUp)
Else
Set myCell = Cells(ActiveCell.Row, 1)
End If

With myCell.Resize(4).EntireRow
If MsgBox("Above = ""Yes"", Below = ""No""", vbYesNo) = vbYes Then
.Copy
.Insert
myCell.Offset(-4).Value = newFName
myCell.Offset(-4,1).Value = newLName
Else
.Copy
.Offset(4).Insert
myCell.Offset(4).Value = newName
myCell.Offset(4,1).Value = newLName
End If
End With

Application.CutCopyMode = False
End Sub


"Arnold" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi All,
> In a scheduling worksheet, first and last names are listed in the left
> columns, formulas in columns to the right of the names, and then
> individual days are added daily in columns to the right of the
> formulas. Marks are made in the intersections of days and people.
>
> I would like to have a cmd button or menu to add new people. The
> tricky part is that 1 person's info occupies 4 consecutive rows. Each
> row contains different formulas that would have to be included in the
> copy. It would be best if users could click on any 1 of the 4 rows of
> an existing person's record, and then click the cmd to add a new
> person's record (4 new rows) either above or below the existing
> person's record. Code would have to recognize where one group of rows
> starts and ends.
>
> Note that each person's name is put on the top row (cols A and B) of
> his or her record, so the presence of text 0 or 1 or 2 or 3 lines
> above where the cursor is could be the identifying feature that could
> maybe make this happen. I just don't know how. Any help would be
> greatly appreciated.
>
> Thanks,
> Eric
>

Thanks for responding Bernie. I will have time to try this out later
today and will post back the results.
Eric

I tried this code and it will allow a user to insert a row for a new
person below or above the current cell position.

However, if the current cell is within another person's record (4
consecutive rows), the new row will split this and be inserted within
the record. I would like for the new person's row to be entered
either before the current record's top row (contains the the person's
first and last names in cols A and B), or below the current record's
last row (3 rows down from the row with the first and last names in
cols A and B).

Also, the code above only copies formulas for the current row and
carries them into a new row--but each of the 4 rows of a person's
record contain different formulas. I would like for code to carry all
of the formulas in the 4 different rows into 4 new rows properly--
formulas should remain but values should revert back to null or
default values.

I thought about putting a template range of 4 rows containing
formatting and formulas on a separate sheet, but this won't work
because days will be added in the main sheet, so the template will not
match up when inserted into the schedule sheet with code.

Thanks again
Eric

Arnold,

If you are seeing the behavior that you describe, then the first column of
your record isn't truly blank.

Try selecting a cell in one of those rows and running this macro:

Sub Test()
MsgBox IIf(Cells(ActiveCell.Row, 1).Value = "", _
"The cell is blank", "The cell only looks blank")
End Sub

You will need to delete the spaces or other values in the seemingly blank
cells.

HTH,
Bernie
MS Excel MVP

"Arnold" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I tried this code and it will allow a user to insert a row for a new
> person below or above the current cell position.
>
> However, if the current cell is within another person's record (4
> consecutive rows), the new row will split this and be inserted within
> the record. I would like for the new person's row to be entered
> either before the current record's top row (contains the the person's
> first and last names in cols A and B), or below the current record's
> last row (3 rows down from the row with the first and last names in
> cols A and B).
>
> Also, the code above only copies formulas for the current row and
> carries them into a new row--but each of the 4 rows of a person's
> record contain different formulas. I would like for code to carry all
> of the formulas in the 4 different rows into 4 new rows properly--
> formulas should remain but values should revert back to null or
> default values.
>
> I thought about putting a template range of 4 rows containing
> formatting and formulas on a separate sheet, but this won't work
> because days will be added in the main sheet, so the template will not
> match up when inserted into the schedule sheet with code.
>
> Thanks again
> Eric
>

Ah, you're right. All 4 rows of of a person's record in my test
spreadsheet had the person's name (as in a non-normal flat structure)
for sorting purposes. Thanks Bernie.