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" wrote:
> 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
>
>
Similar Threads
