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
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
