excel_n00bi3
New Member
- Joined
- Sep 3, 2015
- Messages
- 5
Hi guys! I'm hoping someone can assist me in my quest to complete my database. Everything is done, except for copying a row to a different worksheet based on a combobox selection (on my userform).
Question 1 - Would it be easier to copy cell data (Column, A - G) to the secondary worksheet through my userform, or after it has been plotted onto my worksheet? The combobox only has two selections (hot or high).
Currently - this data entered on the userform is pushed to a "mastersheet" - but i'd like it also broken into whether the data falls under HOT or HIGH on their own sheet. This database will carry approximately 2000 entries on the master sheet. I am unable to forecast how many will be HOT, or how many will be HIGH.
If the answer to question 1 is that it'd be easier to add a line or two through the userform - then please see below:
(I have about 180 lines of code on this sheet... so i'm not going to paste it all on here)
Private Sub cbOk_click()
Dim rowcount As Long
Dim ctl As Control
' Check user input
If Me.tbinitiator.Value = "" Then
MsgBox "Please enter the permit initiator's name.", vbExclamation, "New Permit"
Me.tbinitiator.SetFocus
Exit Sub
End If
If Me.cbtype.Value = "" Then
MsgBox "Please select the type of permit.", vbExclamation, "New Permit"
Me.cbtype.SetFocus
Exit Sub
End If
If (Me.tbdescription.Value) = "" Then
MsgBox "Please enter a description.", vbExclamation, "New Permit"
Me.tbdescription.SetFocus
Exit Sub
End If
' Write data to worksheet
Dim lngWriteRow As Long
Dim WS As Worksheet
Set WS = Worksheets("Master List - ALL PERMITS")
lngWriteRow = WS.Cells(Rows.Count, 2) _
.End(xlUp).Offset(1, 0).Row
If lngWriteRow < 2 Then lngWriteRow = 2
WS.Range("B" & lngWriteRow) = Me.cbtype.Value <~~~~ Combobox Selection (Hot or High)
WS.Range("C" & lngWriteRow) = Me.tbinitiator.Value
WS.Range("F" & lngWriteRow) = Me.tbdescription.Value
If Me.CheckBox1.Value = True Then
WS.Range("D" & lngWriteRow) = Now()
End If
If Me.CheckBox1.Value = False Then
WS.Range("D" & lngWriteRow) = Me.tbstart.Value
End If
If Me.CheckBox2.Value = True Then
WS.Range("E" & lngWriteRow) = Now()
End If
If Me.CheckBox2.Value = False Then
WS.Range("E" & lngWriteRow) = Me.tbend.Value
End If
The only issue with running it through the userform, is that the data collected on on this form is pushed to the next available row - starting at column B. Column A on the mastersheet provides a index number - that is used to reference the work being done.
It seems easier that once the data is plotted on the master sheet - that it copies the entire row, minus B, (A, C, D, E, F, G, H) - depending on selection in Column B - and pasting it to a defined (2nd) worksheet. I just dont know how to do it!
Looking to have:
A (mastersheet) --> A (secondary)
C (mastersheet) --> B (secondary)
D (mastersheet) --> C (secondary)
E (mastersheet) --> D (secondary)
F (mastersheet) --> E (secondary)
G (mastersheet) --> F (secondary)
H (mastersheet) --> G (secondary)
-- removed inline image ---
Question 1 - Would it be easier to copy cell data (Column, A - G) to the secondary worksheet through my userform, or after it has been plotted onto my worksheet? The combobox only has two selections (hot or high).
Currently - this data entered on the userform is pushed to a "mastersheet" - but i'd like it also broken into whether the data falls under HOT or HIGH on their own sheet. This database will carry approximately 2000 entries on the master sheet. I am unable to forecast how many will be HOT, or how many will be HIGH.
If the answer to question 1 is that it'd be easier to add a line or two through the userform - then please see below:
(I have about 180 lines of code on this sheet... so i'm not going to paste it all on here)
Private Sub cbOk_click()
Dim rowcount As Long
Dim ctl As Control
' Check user input
If Me.tbinitiator.Value = "" Then
MsgBox "Please enter the permit initiator's name.", vbExclamation, "New Permit"
Me.tbinitiator.SetFocus
Exit Sub
End If
If Me.cbtype.Value = "" Then
MsgBox "Please select the type of permit.", vbExclamation, "New Permit"
Me.cbtype.SetFocus
Exit Sub
End If
If (Me.tbdescription.Value) = "" Then
MsgBox "Please enter a description.", vbExclamation, "New Permit"
Me.tbdescription.SetFocus
Exit Sub
End If
' Write data to worksheet
Dim lngWriteRow As Long
Dim WS As Worksheet
Set WS = Worksheets("Master List - ALL PERMITS")
lngWriteRow = WS.Cells(Rows.Count, 2) _
.End(xlUp).Offset(1, 0).Row
If lngWriteRow < 2 Then lngWriteRow = 2
WS.Range("B" & lngWriteRow) = Me.cbtype.Value <~~~~ Combobox Selection (Hot or High)
WS.Range("C" & lngWriteRow) = Me.tbinitiator.Value
WS.Range("F" & lngWriteRow) = Me.tbdescription.Value
If Me.CheckBox1.Value = True Then
WS.Range("D" & lngWriteRow) = Now()
End If
If Me.CheckBox1.Value = False Then
WS.Range("D" & lngWriteRow) = Me.tbstart.Value
End If
If Me.CheckBox2.Value = True Then
WS.Range("E" & lngWriteRow) = Now()
End If
If Me.CheckBox2.Value = False Then
WS.Range("E" & lngWriteRow) = Me.tbend.Value
End If
The only issue with running it through the userform, is that the data collected on on this form is pushed to the next available row - starting at column B. Column A on the mastersheet provides a index number - that is used to reference the work being done.
It seems easier that once the data is plotted on the master sheet - that it copies the entire row, minus B, (A, C, D, E, F, G, H) - depending on selection in Column B - and pasting it to a defined (2nd) worksheet. I just dont know how to do it!
Looking to have:
A (mastersheet) --> A (secondary)
C (mastersheet) --> B (secondary)
D (mastersheet) --> C (secondary)
E (mastersheet) --> D (secondary)
F (mastersheet) --> E (secondary)
G (mastersheet) --> F (secondary)
H (mastersheet) --> G (secondary)
-- removed inline image ---
Last edited: