Adding Several Rows at a time and Asking for User Input

[Arnold]

Hello all,

Have a workbook with 2 sheets--one named "Fields" containing formatted
columns, rows, etc., and another called "Data" containing the data. I
would like the following functionality when adding a new record in Data
(one record consists of 4 rows):

1. Copy the group of 4 formatted rows in the Fields sheet (rows 13
through 16),
2. Activate the Data sheet,
3. Have the user click on a cell in column A above where he or she
wants the 4 rows to be inserted,
4. Insert the 4 rows (adding rows if placed between existing records
so as to not overwrite data),
5. Ask the user for the value of the cells in column A (A-cells in the
4 rows will all be the same value),
6. Ask the user for the value of the cells in column B (all 4 B-cells
will also be the same), and
7. Have Excel fill in the cells with the values.

Also, when inserting the new record (4 rows), a user may happen to
click on any of the rows or cells in column A of the same record, so
the new record would have to be placed below the last used row of the
record.

Hope this makes sense; any help would be greatly appreciated.

Eric

 

[Guest]

This is all untried, untested, but is provided to to give you an idea of
the approach to the solution.


1. Copy the group of 4 formatted rows in the Fields sheet (rows 13
through 16),

Worksheets("Fields).Range("A13:A16).EntireRow.Copy

2. Activate the Data sheet,

Worksheets("Data").Activate


3. Have the user click on a cell in column A above where he or she
wants the 4 rows to be inserted,

LastRow = Cells(Rows.Count,1).End(xlUp).Row
Response = MsgBox("The last row containing data is " & LastRow & ". Do
you want to add the records in the next available
rows?", vbYesNo, "Destination Option"
If Response = vbYes Then
Cells(LastRow + 1,1).Activate
ActiveSheet.Paste
Else
InsOpt = Application.InputBox("Enter a cell in A1
format - example: "A15" - where you want to insert
the records.", "Select Insetion Point", Type:=8)
End If



4. Insert the 4 rows (adding rows if placed between existing records
so as to not overwrite data),

If InsOpt <> Cancel Or InsOPt <> "" Then
Range(Cells(Range(InsOpt).Offset(1,0),1),
Cells(Range(InsOpt).Offset(4,0),1).Select
Selection.EntireRow.Insert
Range(InsOpt).Offset(1,0).Activate
ActiveSheet.Paste
End If


5. Ask the user for the value of the cells in column A (A-cells in the
4 rows will all be the same value),

aVal = InputBox("Enter the value for column A of the inserted records.",
"Col A Values"

6. Ask the user for the value of the cells in column B (all 4 B-cells
will also be the same), and
bVal = InputBox("Enter the value for column B of the inserted records.",
"Col B values"

7. Have Excel fill in the cells with the values.

Range(Cells(Range(InsOpt).Offset(1,0),1),
Cells(Range(InsOpt).Offset(4,0),1) = aVal
Range(Cells(Range(InsOpt).Offset(1,0),2),
Cells(Range(InsOpt).Offset(4,0),2) = bVal



Also, when inserting the new record (4 rows), a user may happen to
click on any of the rows or cells in column A of the same record, so
the new record would have to be placed below the last used row of the
record.

I'm not sure what the last paragraph means.

 

[Arnold]

Lots of Thanks JLGWhiz--will give this a try later on...