Automatically add Data to a New row

I have columns that have formulas which are not part of the userform.  When I add a new record only the fields from the user form are added to the new row.  However, I have columns with formulas that need to be copied down once a new record is added.

Currently the columns that need to be copied down are col O and then T thru AB.  However, I would like any new added information that is not part of the form to be added automatically.

In the attached file I highlighted in yellow  what column rows need to be added each time a new record is added. In this attachment there are no formulas but in the original these columns are formulated.
29136966e-v1.07ab.xlsm
MelbutAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
Try updating WriteToSheet to this.
Sub WriteToSheet()

    If bEdit = True Then
        lRw = rData.Rows.Count + 1
    Else
        lRw = Me.lbxData.ListIndex + 2
    End If

    For iX = 1 To 13
        rData.Cells(lRw, iX).Value = Me("editstudent" & iX).Value
    Next iX

    For iX = 16 To 19
        rData.Cells(lRw, iX).Value = Me("editstudent" & iX - 2).Value
    Next iX

    If bEdit = True Then
        rData.Cells(lRw - 1, "O").Copy rData.Cells(lRw, "O")
        rData.Cells(lRw - 1, "T").Resize(, 9).Copy rData.Cells(lRw, "T")
    End If
    
    rData.Columns.AutoFit
    ColumnWidths rData

    Me.chkNew.Value = False
    
End Sub

Open in new window

MelbutAuthor Commented:
Noire,  I am not that familiar with VBA where do I put this code? Into which module or sheet?
NorieAnalyst Assistant Commented:
It replaces the existing WriteSheet sub in the module attached to the form named 'frmeditrecord'.

To access that module open the VBE (ALT+F11), select the form 'frmeditrecord' in the Project Explorer pane on the left, right click and select View Code.

in the left hand drop down at the top of the code window select (General) and from the right have drop down select WriteToSheet.

You should now see the WriteToSheet sub and that's the code you need to replace with what I posted.
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

MelbutAuthor Commented:
Thank you.  It worked perfectly.  However, I thought I gave all the columns.  Can you add to the code Col N and Q to be pull down as well.
NorieAnalyst Assistant Commented:
This is updated to include columns N and Q.
Sub WriteToSheet()

    If bEdit = True Then
        lRw = rData.Rows.Count + 1
    Else
        lRw = Me.lbxData.ListIndex + 2
    End If

    For iX = 1 To 13
        rData.Cells(lRw, iX).Value = Me("editstudent" & iX).Value
    Next iX

    For iX = 16 To 19
        rData.Cells(lRw, iX).Value = Me("editstudent" & iX - 2).Value
    Next iX

    If bEdit = True Then
        rData.Cells(lRw - 1, "N").Resize(,2).Copy rData.Cells(lRw, "N")
        rData.Cells(lRw - 1, "Q").Copy rData.Cells(lRw, "Q")
        rData.Cells(lRw - 1, "T").Resize(, 9).Copy rData.Cells(lRw, "T")
    End If
    
    rData.Columns.AutoFit
    ColumnWidths rData

    Me.chkNew.Value = False
    
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MelbutAuthor Commented:
Excellent!  Thank you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.