Insert row

[Jim G]

I have a template that copies fromulas from a hidden row after an entry in
col A and moves the curser down to the next line. This means I don't need to
know how many lines are required to complete the data entry. However I have
added a total line below the data entry line.

I want to modify the following code (kindly provided by Bernie Deitrick) to
insert a row to move the total line down so as the new data line follows the
one above.

Private Sub Worksheet_Change(ByVal target As Range)
If target.Cells.Count > 1 Then Exit Sub
If target.Column <> 5 Then Exit Sub 'last data entry cell
If target.Row < 6 Then Exit Sub 'starting row following hidden formula row

If target.Offset(0, 1).Value <> "" Then
If MsgBox("You are overwrititng existing data. Are you sure?", vbYesNo) =
vbNo Then
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
Exit Sub
End If
End If


Application.EnableEvents = False
Cells(target.Row + 1, 1).Select
'MsgBox "Range" & target.Address & "was changed"

Range("F5:Q5").Copy target.Offset(0, 1).Resize(1, 12) 'formula row to copy

Application.EnableEvents = True

End Sub

 

[Joel]

The solution isn't to move the total row because if you insert a row the
formula will Change automatically. except if you add a row immediately before
the total row. The solution is to change this line slightly to handle a
different amount of row in the data

from
If target.Row < 6 Then Exit Sub 'starting row following hidden formula row
to
LastRow = Range("A" & Rows.count).end(xlup).row
If target.Row < (LastRow - 1) Then Exit Sub 'starting row following
hidden
'formula
row


I made the code work with any added Rows between row 2 and the hidden row
which I assume is the last row

Private Sub Worksheet_Change(ByVal target As Range)
If target.Row = 1 Then Exit Sub
If target.Cells.Count > 1 Then Exit Sub

If target.Column <> 5 Then Exit Sub 'last data entry cell

LASTROW = Range("F" & Rows.Count).End(xlUp).Row
If target.Row > LASTROW Then Exit Sub 'starting row following hidden
formula row

If target.Offset(0, 1).Value <> "" Then
Response = MsgBox("You are overwrititng existing data. " & _
Are you sure?", vbYesNo)
If Response = vbNo Then
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
Exit Sub
End If
End If


Application.EnableEvents = False
'MsgBox "Range" & target.Address & "was changed"

Range("F" & LASTROW).Copy _
Destination:=Range("F2:F" & LASTROW) 'formula row to copy

Application.EnableEvents = True

End Sub

 

[Jim G]

Thanks Joel,

I haven't had a chance to try this code (not back at work yet) but ask you
consider the following:

The data entry starts on row 6 and the last data entry is in cell E6. The
formula row is row 5 and is copied to row 6 when data is entered in E6.
There is summary information at the end of the data rows that summarise the
data above. Currently the data is added to each row below row 5 and will
eventually overwrite the summary data. I want to place the summary rows
immediately below the new data (row 7 in my template) and have the macro
insert a row immediately below row 6 so that the new row is empty ready for
new data (row 7) and the totals become row 8.

The object ive is to have a data set (form) that only has data rows without
a need for me to limit the size or fix it to a page length

Jim

 

[Joel]

I don't think you want to use a worksheet change function for this task.
Since you are entering data in multiple columns and probably changing the
data when you find mistakes; the question beomes when do you add a new row?
A workshet change wil keep on adding new rows when you don't want to add new
row.

the better solution is just to put a control buttonm on the worksheet (or in
the toolbar) that just adds the button when yo want to add a new row. the
macro will look for the total row and add the new row one row before the
total row and copy the formula from row 5 into the new row.

 

[Jim G]

It 's a spreadshet for creating a petty reconciliation so it lLooks like the
easy way is to add the summary to the top of the page and allow the macro to
add rows until finished. Then I don't need to worry about inserting lines
and the Macro works as orginally intended.

Thanks anyway.

Cheers