Add Row Macro with selection from Form

nanders8

New Member
Joined
Sep 19, 2014
Messages
27
I am trying to get a new row to add based on criteria selected from a form.

The form has months, January through December. If the user picks a month from the form, it will add a row to that specific month.

Right now this is copying a row from a Master sheet and pasting it on the active sheet for that month.

Example: if I select January from the form, it will copy 1 row of data from the Master sheet, and then go to my active sheet and paste that into the January section.

The code I am using is all messed up and not working like it should. I would really appreciate someone to guide me in what I am doing wrong with the code.

Here is what I have for the code. This is assigned to a button on the excel sheet, that when pressed, the form appears, the user selects their month, and then presses ok to fulfill the command.

The sheet 2016 is not the only sheet that is in the form, it could be a sheet labeled 2015 or one labeled 2004. Depends on what sheet they are needing to fill out and add data to.

Private Sub CommandButton1_Click()
Dim choice
Dim lr
Dim findit As Object

choice = monthbox.Value

lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

Set findit = ActiveSheet.Range("A7", (Cells(lr, 1))).Find(what:=choice)
If findit Is Nothing Then
MsgBox ("Please try again, the varbiable dimensions were incorrectly set when the HUMINT selection was processed.")
Else
Sheets("Master").Visible = True
findit.Select
Selection.End(xlDown).Select
Selection.EntireRow.Insert
Sheets("Master").Select
Range("A9:G9").Select
Selection.Copy
Sheets("2016").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("Master").Visible = False
End If

AddRow.Hide
monthbox.Value = ""
ActiveCell.Offset(0, 1).Select
End Sub

Private Sub CommandButton2_Click()
AddRow.Hide
monthbox.Value = ""
End Sub

Private Sub UserForm_Initialize()
With monthbox
.AddItem "January"
.AddItem "February"
.AddItem "March"
.AddItem "April"
.AddItem "May"
.AddItem "June"
.AddItem "July"
.AddItem "August"
.AddItem "September"
.AddItem "October"
.AddItem "November"
.AddItem "December"
End With
End Sub
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Watch MrExcel Video

Forum statistics

Threads
1,122,865
Messages
5,598,525
Members
414,245
Latest member
Major Aly

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
Top