adding row from userfrom and copying formulas
 

I have the following code that will add a record to the next blank row.


Private Sub cmdAdd_Click()
' Save form contents before changing rows:
SaveRow

' Set current row to first empty row, i.e. one row after
' the last row currently in use:
If Cells(1, 1).Value = "" Then
lCurrentRow = 1 ' (list is empty - start in row 1)
Else
lCurrentRow = ActiveSheet.UsedRange.Rows.Count + 1
End If

' Clear the form for user to add new name:
LoadRow

' Set focus to Name textbox:
txtReqNum.SetFocus
End Sub

What I would like to do is instead of add the userform data to the
blank row is to add a new row and add the userform data to that new
row.

I have existing data in columns A:O (this is also where the new data
from the userform will be added, but in the newly added row) and I have
hidden formulas that counts up the various values of A:O in columns
P:AG. I need the formulas from the previous row that currently has
existing hidden formulas (P:AG) copied to the new row that we just
added in the same range (P:AG). I also have the sums of colums P:AG
added up in the row below the new added row in the same range P:AG in
each of those columns and need the sum formula updated to reflect the
counts of the newly added row data.

I know this may be very complicated (it is for me at least) so any help
would be greatly appreciated. Thanks.

adding row from userfrom and copying formulas
 

Hi Criss,

I cooked the procedure below.

But before you use this make usure that there is a blank line between
the figures and the SUM formulas in P-AG. The blank line MUSR be
included in the formulas.

So if the last row with figures is line 23, line 24 will be blank.
the formula for P25 looks like =SUM(P1:P24)

Now you cab use this procedure

Private Sub cmdAddRow_Click()
Dim myNewRow As Long
Dim myFormulas As Long

myNewRow = Range("A4").End(xlDown).Offset(1, 0).Row

Cells(myNewRow, 1).EntireRow.Insert shift:=xlDown

Cells(myNewRow, 1).Value = Form1.TextBox1.Text
Cells(myNewRow, 2).Value = Form1.TextBox2.Text
Cells(myNewRow, 3).Value = Form1.TextBox3.Text
Cells(myNewRow, 4).Value = Form1.TextBox4.Text
Cells(myNewRow, 5).Value = Form1.TextBox5.Text
Cells(myNewRow, 6).Value = Form1.TextBox6.Text
Cells(myNewRow, 7).Value = Form1.TextBox7.Text
Cells(myNewRow, 8).Value = Form1.TextBox8.Text
Cells(myNewRow, 9).Value = Form1.TextBox9.Text
Cells(myNewRow, 10).Value = Form1.TextBox10.Text
Cells(myNewRow, 11).Value = Form1.TextBox12.Text
Cells(myNewRow, 12).Value = Form1.TextBox12.Text
Cells(myNewRow, 13).Value = Form1.TextBox13.Text
Cells(myNewRow, 14).Value = Form1.TextBox14.Text
Cells(myNewRow, 15).Value = Form1.TextBox15.Text

For myFormulas = 16 To 33
Cells(myNewRow, myFormulas).Formula = Cells(myNewRow - 1,
myFormulas).Formula
Next

End Sub


HTH,

RadarEye

adding row from userfrom and copying formulas
 

Thanks!!! - works like a charm!

RadarEye wrote:
Hi Criss,

I cooked the procedure below.

But before you use this make usure that there is a blank line between
the figures and the SUM formulas in P-AG. The blank line MUSR be
included in the formulas.

So if the last row with figures is line 23, line 24 will be blank.
the formula for P25 looks like =SUM(P1:P24)

Now you cab use this procedure

Private Sub cmdAddRow_Click()
Dim myNewRow As Long
Dim myFormulas As Long

myNewRow = Range("A4").End(xlDown).Offset(1, 0).Row

Cells(myNewRow, 1).EntireRow.Insert shift:=xlDown

Cells(myNewRow, 1).Value = Form1.TextBox1.Text
Cells(myNewRow, 2).Value = Form1.TextBox2.Text
Cells(myNewRow, 3).Value = Form1.TextBox3.Text
Cells(myNewRow, 4).Value = Form1.TextBox4.Text
Cells(myNewRow, 5).Value = Form1.TextBox5.Text
Cells(myNewRow, 6).Value = Form1.TextBox6.Text
Cells(myNewRow, 7).Value = Form1.TextBox7.Text
Cells(myNewRow, 8).Value = Form1.TextBox8.Text
Cells(myNewRow, 9).Value = Form1.TextBox9.Text
Cells(myNewRow, 10).Value = Form1.TextBox10.Text
Cells(myNewRow, 11).Value = Form1.TextBox12.Text
Cells(myNewRow, 12).Value = Form1.TextBox12.Text
Cells(myNewRow, 13).Value = Form1.TextBox13.Text
Cells(myNewRow, 14).Value = Form1.TextBox14.Text
Cells(myNewRow, 15).Value = Form1.TextBox15.Text

For myFormulas = 16 To 33
Cells(myNewRow, myFormulas).Formula = Cells(myNewRow - 1,
myFormulas).Formula
Next

End Sub


HTH,

RadarEye

adding row from userfrom and copying formulas
 

One last thing...

What if I had a formula in column 3 that I also needed to have copied
to the new row?

RadarEye wrote:
Hi Criss,

I cooked the procedure below.

But before you use this make usure that there is a blank line between
the figures and the SUM formulas in P-AG. The blank line MUSR be
included in the formulas.

So if the last row with figures is line 23, line 24 will be blank.
the formula for P25 looks like =SUM(P1:P24)

Now you cab use this procedure

Private Sub cmdAddRow_Click()
Dim myNewRow As Long
Dim myFormulas As Long

myNewRow = Range("A4").End(xlDown).Offset(1, 0).Row

Cells(myNewRow, 1).EntireRow.Insert shift:=xlDown

Cells(myNewRow, 1).Value = Form1.TextBox1.Text
Cells(myNewRow, 2).Value = Form1.TextBox2.Text
Cells(myNewRow, 3).Value = Form1.TextBox3.Text
Cells(myNewRow, 4).Value = Form1.TextBox4.Text
Cells(myNewRow, 5).Value = Form1.TextBox5.Text
Cells(myNewRow, 6).Value = Form1.TextBox6.Text
Cells(myNewRow, 7).Value = Form1.TextBox7.Text
Cells(myNewRow, 8).Value = Form1.TextBox8.Text
Cells(myNewRow, 9).Value = Form1.TextBox9.Text
Cells(myNewRow, 10).Value = Form1.TextBox10.Text
Cells(myNewRow, 11).Value = Form1.TextBox12.Text
Cells(myNewRow, 12).Value = Form1.TextBox12.Text
Cells(myNewRow, 13).Value = Form1.TextBox13.Text
Cells(myNewRow, 14).Value = Form1.TextBox14.Text
Cells(myNewRow, 15).Value = Form1.TextBox15.Text

For myFormulas = 16 To 33
Cells(myNewRow, myFormulas).Formula = Cells(myNewRow - 1,
myFormulas).Formula
Next

End Sub


HTH,

RadarEye

adding row from userfrom and copying formulas
 

Hi Chris,

Sorry for the late reply.

You have now:
For myFormulas = 16 To 33
Cells(myNewRow, myFormulas).Formula = Cells(myNewRow - 1,
myFormulas).Formula
Next

just add 1 line:
Cells(myNewRow, 3).Formula = Cells(myNewRow - 1, 3).Formula

HTH

RadarEye.



Chris wrote:
One last thing...

What if I had a formula in column 3 that I also needed to have copied
to the new row?

RadarEye wrote:
Hi Criss,

I cooked the procedure below.

But before you use this make usure that there is a blank line between
the figures and the SUM formulas in P-AG. The blank line MUSR be
included in the formulas.

So if the last row with figures is line 23, line 24 will be blank.
the formula for P25 looks like =SUM(P1:P24)

Now you cab use this procedure

Private Sub cmdAddRow_Click()
Dim myNewRow As Long
Dim myFormulas As Long

myNewRow = Range("A4").End(xlDown).Offset(1, 0).Row

Cells(myNewRow, 1).EntireRow.Insert shift:=xlDown

Cells(myNewRow, 1).Value = Form1.TextBox1.Text
Cells(myNewRow, 2).Value = Form1.TextBox2.Text
Cells(myNewRow, 3).Value = Form1.TextBox3.Text
Cells(myNewRow, 4).Value = Form1.TextBox4.Text
Cells(myNewRow, 5).Value = Form1.TextBox5.Text
Cells(myNewRow, 6).Value = Form1.TextBox6.Text
Cells(myNewRow, 7).Value = Form1.TextBox7.Text
Cells(myNewRow, 8).Value = Form1.TextBox8.Text
Cells(myNewRow, 9).Value = Form1.TextBox9.Text
Cells(myNewRow, 10).Value = Form1.TextBox10.Text
Cells(myNewRow, 11).Value = Form1.TextBox12.Text
Cells(myNewRow, 12).Value = Form1.TextBox12.Text
Cells(myNewRow, 13).Value = Form1.TextBox13.Text
Cells(myNewRow, 14).Value = Form1.TextBox14.Text
Cells(myNewRow, 15).Value = Form1.TextBox15.Text

For myFormulas = 16 To 33
Cells(myNewRow, myFormulas).Formula = Cells(myNewRow - 1,
myFormulas).Formula
Next

End Sub


HTH,

RadarEye