Macro to add new row of data

R

ryan james hardy

New Member
Joined
Sep 12, 2012
Messages
19
Hi, i am looking for a macro that adds a new row of data. For example i have these rows below, where orange is input and white is derived from a formula. So ideally the macro would be assigned to a button which when clicked would have fields for the 8 orange cells and then automatically copy down the formula of the 2 white cells.


[TABLE="width: 1101"]
<colgroup><col style="width: 152pt; mso-width-source: userset; mso-width-alt: 6464;" width="202"> <col style="width: 54pt;" width="72"> <col style="width: 71pt; mso-width-source: userset; mso-width-alt: 3040;" width="95"> <col style="width: 59pt; mso-width-source: userset; mso-width-alt: 2496;" width="78"> <col style="width: 104pt; mso-width-source: userset; mso-width-alt: 4448;" width="139"> <col style="width: 74pt; mso-width-source: userset; mso-width-alt: 3136;" span="2" width="98"> <col style="width: 107pt; mso-width-source: userset; mso-width-alt: 4576;" width="143"> <col style="width: 131pt; mso-width-source: userset; mso-width-alt: 5568;" width="174"> <col style="width: 159pt; mso-width-source: userset; mso-width-alt: 6784;" width="212"> <col style="width: 54pt;" width="72"> <col style="width: 62pt; mso-width-source: userset; mso-width-alt: 2656;" width="83"> <tbody>[TR]
[TD="class: xl65, width: 202, bgcolor: transparent"]Staff[/TD]
[TD="class: xl65, width: 72, bgcolor: transparent"]Gender[/TD]
[TD="class: xl65, width: 95, bgcolor: transparent"]DOB[/TD]
[TD="class: xl65, width: 78, bgcolor: transparent"]Age[/TD]
[TD="class: xl65, width: 139, bgcolor: transparent"]Business unit number[/TD]
[TD="class: xl66, width: 98, bgcolor: transparent"]Type[/TD]
[TD="class: xl66, width: 98, bgcolor: transparent"]Start date[/TD]
[TD="class: xl66, width: 143, bgcolor: transparent"]Termination date[/TD]
[TD="class: xl65, width: 174, bgcolor: transparent"]Weeks employed[/TD]
[TD="class: xl65, width: 212, bgcolor: transparent"]Reason for termination[/TD]
[TD="class: xl67, width: 72, bgcolor: transparent"][/TD]
[TD="class: xl67, width: 83, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #FFCC99"]Emma[/TD]
[TD="class: xl68, bgcolor: #FFCC99"]F[/TD]
[TD="class: xl69, bgcolor: #FFCC99"]7/05/1965[/TD]
[TD="class: xl70, bgcolor: #F2F2F2"]47[/TD]
[TD="class: xl68, bgcolor: #FFCC99"]649[/TD]
[TD="class: xl68, bgcolor: #FFCC99"]Casual[/TD]
[TD="class: xl69, bgcolor: #FFCC99"]1/05/2011[/TD]
[TD="class: xl71, bgcolor: #FFCC99"]13/01/2012[/TD]
[TD="class: xl70, bgcolor: #F2F2F2"]37[/TD]
[TD="class: xl68, bgcolor: #FFCC99"]Termination with cause[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #FFCC99"]Michele[/TD]
[TD="class: xl68, bgcolor: #FFCC99"]F[/TD]
[TD="class: xl69, bgcolor: #FFCC99"]4/01/1985[/TD]
[TD="class: xl70, bgcolor: #F2F2F2"]27[/TD]
[TD="class: xl68, bgcolor: #FFCC99"]671[/TD]
[TD="class: xl68, bgcolor: #FFCC99"]Casual[/TD]
[TD="class: xl69, bgcolor: #FFCC99"]4/05/2011[/TD]
[TD="class: xl71, bgcolor: #FFCC99"]6/03/2012[/TD]
[TD="class: xl70, bgcolor: #F2F2F2"]44[/TD]
[TD="class: xl68, bgcolor: #FFCC99"]Other[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, width: 83, bgcolor: transparent"]<v:shapetype id="_x0000_t75" stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" o:preferrelative="t" o:spt="75" coordsize="21600,21600"> <v:stroke joinstyle="miter"> <v:formulas> <v:f eqn="if lineDrawn pixelLineWidth 0"> <v:f eqn="sum @0 1 0"> <v:f eqn="sum 0 0 @1"> <v:f eqn="prod @2 1 2"> <v:f eqn="prod @3 21600 pixelWidth"> <v:f eqn="prod @3 21600 pixelHeight"> <v:f eqn="sum @0 0 1"> <v:f eqn="prod @6 1 2"> <v:f eqn="prod @7 21600 pixelWidth"> <v:f eqn="sum @8 21600 0"> <v:f eqn="prod @7 21600 pixelHeight"> <v:f eqn="sum @10 21600 0"> </v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:formulas> <v:path o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"> <o:lock aspectratio="t" v:ext="edit"> </o:lock></v:path></v:stroke></v:shapetype>[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #FFCC99"]John[/TD]
[TD="class: xl68, bgcolor: #FFCC99"]M[/TD]
[TD="class: xl69, bgcolor: #FFCC99"]9/05/1963[/TD]
[TD="class: xl70, bgcolor: #F2F2F2"]49[/TD]
[TD="class: xl68, bgcolor: #FFCC99"]670[/TD]
[TD="class: xl68, bgcolor: #FFCC99"]Permanent[/TD]
[TD="class: xl69, bgcolor: #FFCC99"]5/05/2010[/TD]
[TD="class: xl69, bgcolor: #FFCC99"]2/04/2012[/TD]
[TD="class: xl70, bgcolor: #F2F2F2"]99.8[/TD]
[TD="class: xl68, bgcolor: #FFCC99"]Resignation[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #FFCC99"]Stevo[/TD]
[TD="class: xl68, bgcolor: #FFCC99"]M[/TD]
[TD="class: xl69, bgcolor: #FFCC99"]4/06/1970[/TD]
[TD="class: xl70, bgcolor: #F2F2F2"]42[/TD]
[TD="class: xl68, bgcolor: #FFCC99"]649[/TD]
[TD="class: xl68, bgcolor: #FFCC99"]Casual[/TD]
[TD="class: xl69, bgcolor: #FFCC99"]6/05/2011[/TD]
[TD="class: xl69, bgcolor: #FFCC99"]5/05/2012[/TD]
[TD="class: xl70, bgcolor: #F2F2F2"]52.2[/TD]
[TD="class: xl68, bgcolor: #FFCC99"]Retirement[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #FFCC99"]Mark[/TD]
[TD="class: xl68, bgcolor: #FFCC99"]M[/TD]
[TD="class: xl69, bgcolor: #FFCC99"]6/09/1959[/TD]
[TD="class: xl70, bgcolor: #F2F2F2"]53[/TD]
[TD="class: xl68, bgcolor: #FFCC99"]672[/TD]
[TD="class: xl68, bgcolor: #FFCC99"]Part time[/TD]
[TD="class: xl69, bgcolor: #FFCC99"]6/05/2011[/TD]
[TD="class: xl69, bgcolor: #FFCC99"]6/05/2012[/TD]
[TD="class: xl70, bgcolor: #F2F2F2"]52.2[/TD]
[TD="class: xl68, bgcolor: #FFCC99"]End of Contract[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]


I tried this code as a test and it somewhat worked but didnt copy the formulas down.


Private Sub CommandButton1_Click()
End Sub
Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Entry")
'find first empty row in database
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
'copy the data to the database
ws.Cells(iRow, 1).Value = Me.Staff.Value
ws.Cells(iRow, 2).Value = Me.MF.Value
ws.Cells(iRow, 3).Value = Me.DOB.Value
ws.Cells(iRow, 5).Value = Me.Unit.Value
ws.Cells(iRow, 6).Value = Me.Type.Value
'clear the data
Me.Staff.Value = ""
Me.MF.Value = ""
Me.DOB.Value = ""
Me.Unit.Value = ""
Me.Type.Value = ""
Me.txtPart.SetFocus
End Sub

Private Sub cmdClose_Click()
Unload Me
End Sub

Private Sub Label1_Click()
End Sub
Private Sub Staff_Click()
End Sub
Private Sub txtPart_Change()
End Sub
Private Sub UserForm_Click()
End Sub
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
Before clearing the data, put in some lines like:
Code: 
ws.Cells(iRow,n).Formula = wsCells(iRow-1,n).Formula
This copies the formula in column numbered n for the row above into the new row.

Hope this helps.

Paul
 
Upvote 0
How about something like this?

Code: 
Sub ryanjameshardy()
'
    Dim lr As Long
    Range("A1:J1").Select
    MsgBox ("Please Leave the Age field and Weeks Employed Fields blank")
    ActiveSheet.ShowDataForm
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    Range("D" & lr - 1).Copy Range("D" & lr)
    Range("I" & lr - 1).Copy Range("I" & lr)
End Sub
 
Upvote 0
DeBeuz said:
Before clearing the data, put in some lines like:
Code: 
ws.Cells(iRow,n).Formula = wsCells(iRow-1,n).Formula
This copies the formula in column numbered n for the row above into the new row.

Hope this helps.

Paul
Click to expand...
Yes, that works great. Thanks. I also was wondering if its possible to limit input to 5 standard entries. For example in the last column (reason for termination) there is five standard responses that should be entered which are, Resignation, Termination, Other, Transfer and End of contract. I've got it working without the macro using a drop down box but i'm wondering if its possible to incorporate a drop down box into the VBA userform?
 
Upvote 0
Actually it kind of doesnt do what i wanted. It copies the exact same formua. For example, if the formula is A1+B1 it will keep copying A1+B1, but i need it to change to A2+B2 when it goes to the next line. Maybe you assumed i knew how to do that, i'llk have a play around. Thanks though
 
Upvote 0
John Davis said:
How about something like this?

Code: 
Sub ryanjameshardy()
'
    Dim lr As Long
    Range("A1:J1").Select
    MsgBox ("Please Leave the Age field and Weeks Employed Fields blank")
    ActiveSheet.ShowDataForm
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    Range("D" & lr - 1).Copy Range("D" & lr)
    Range("I" & lr - 1).Copy Range("I" & lr)
End Sub
Click to expand...

This works great to. I never knew code could be used to make a userform from a header row. I was a bit confused at first because i already made a userform but once i deleted my old form this worked great. I asked DeBeuz about some kind of provision for adding a drop down menu to the last column using a vba userform so the user can only select from a list of responses. thanks for your help.
 
Upvote 0
You're welcome. Glad to help out. I used Range("L1:L5") to create the drop-down list.

Code: 
Sub ryanjameshardy()
'
    Dim lr As Long
    Range("A1:J1").Select
    MsgBox ("Please Leave the Age field and Weeks Employed Fields blank")
    ActiveSheet.ShowDataForm
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    Range("D" & lr - 1).Copy Range("D" & lr)
    Range("I" & lr - 1).Copy Range("I" & lr)
    Range("J" & lr).Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=$L$1:$L$5"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = "Please Select Reason For Termination from the following choices"
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

    
End Sub
 
Upvote 0
Sorry. With corrections:

Code: 
Sub ryanjameshardy()
'
    Dim lr As Long
    Range("A1:J1").Select
    MsgBox ("Please Leave the Age field and Weeks Employed Fields blank")
    ActiveSheet.ShowDataForm
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    Range("D" & lr - 1).Copy Range("D" & lr)
    Range("I" & lr - 1).Copy Range("I" & lr)
    Range("J" & lr).Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=$L$1:$L$5"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = "Please Select Reason For Termination from the following choices"
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    Range("J" & lr).Value = Range("J" & lr).Value
    Range("J" & lr).Validation.Delete
    
End Sub
 
Upvote 0
John Davis said:
Sorry. With corrections:

Code: 
Sub ryanjameshardy()
'
    Dim lr As Long
    Range("A1:J1").Select
    MsgBox ("Please Leave the Age field and Weeks Employed Fields blank")
    ActiveSheet.ShowDataForm
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    Range("D" & lr - 1).Copy Range("D" & lr)
    Range("I" & lr - 1).Copy Range("I" & lr)
    Range("J" & lr).Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=$L$1:$L$5"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = "Please Select Reason For Termination from the following choices"
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    Range("J" & lr).Value = Range("J" & lr).Value
    Range("J" & lr).Validation.Delete
    
End Sub
Click to expand...

Thanks for that. I havn't been able to get it working but i think my excel is playing up. For example, its no longer detecting the header row properly so instaed of the fields boxes being "Staff", "DOB" etc its saying "Emma", "3/4/66". Also, i think it would be best if the five selections i want in a drop down are located in another sheet named "data". How would i refer the userform to the other sheet?
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question Question
Macros - Auto Copy paste row when cell value changes
Replies
7
Views
6K
Fluff
Fluff
N
  • Question Question
excel macro to split data into multiple worksheets based on conditions worksheet
Replies
4
Views
4K
Fluff
Fluff
L
  • Question Question
VBA - Consolidate data and Sum mulitiple ranges
Replies
1
Views
2K
MickG
M
T
  • Question Question
Macro needed to add items to worksheet
Replies
3
Views
1K
onlyadrafter
O
J
  • Question Question
IFANDSUM Questions
2
Replies
12
Views
2K
Eric W
Eric W

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top