Mark J Kubicki said:
copy formulas... thank you
(and I hate to say it, but some folks (esp. upper management, and owners)
are NOT trainable...
....there's got to be a way?
isn't there some sort of on insert event code that I could use to trigger a
code line to copy from ... to the target row?
This'll get you started. It's worksheet event code and needs to go in the
relevant worksheet code module
Option Explicit
Private Changed As Boolean
'
Private Sub Worksheet_Activate()
Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Select
End Sub
'
'
Private Sub Worksheet_Change(ByVal Target As Range)
Changed = True
End Sub
'
'
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'
Dim Cell As Range
'
'**************************
'enter the last FORMULA column below
'(assuming your manual data entry columns are
'all on the left and formulas are on the right)
'
Const LastFormulaCol As String = "M"
'**************************
'
If Target.Row = 1 Then Exit Sub
'
'//if past the last manual entry in this row
'//(we're assuming your data starts in Column B (change to suit))
If Changed = True _
And Target.Row = Range("B" & Rows.Count).End(xlUp).Row _
And Target.HasFormula Then
'
On Error GoTo Finish
ActiveSheet.Unprotect password:=""
'
Application.EnableEvents = False
'
'//insert a new row below
Rows(Target.Row + 1).Insert shift:=xlDown
'
'//copy the row
Rows(Target.Row).Copy
'
'//paste the formats in the new row
Rows(Target.Row + 1).PasteSpecial xlPasteFormats
'
'//copy the formulas to the new row
For Each Cell In Range(Target.Address, LastFormulaCol &
Target.Row)
Cell.Offset(1, 0) = Cell.FormulaR1C1
Next
'
'//select column B in the new row for next entry
Range("B" & Target.Row + 1).Select
Changed = False
End If
'
Finish:
Application.EnableEvents = True
ActiveSheet.Protect password:=""
End Sub
Note: If you have worksheet code below the inserted row... {e.g. let us
assume here it is for Sub Totals and Totals and is of the form
K21=SUM(E9:H20,J9:J20)} then you'll need to change the worksheet formulas so
they're of the form K21=SUM(E9:OFFSET(H21,-1,0),J9:OFFSET(J21,-1,0))
