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.

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

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

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

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