Copying Cell Data (Userform/Sheet1) - to a Secondary Sheet.

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 ---
 
Last edited:

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Hi,
You could create a separate procedure for code that writes data to your sheets. You can then pass the required worksheet object to it from your form.

This is only an example & you will need to develop to meet your project need but something like following maybe:
Form code:
Code:
 Private Sub cbOk_click()  
    Dim ws As Worksheet
    
    ' Write data to worksheets
    For Each ws In Worksheets(Array("Master List - ALL PERMITS", Me.cbtype.Value))
    
        AddRecord sh:=ws, Form:=Me


    Next ws


End Sub


Common procedure ( can be placed in standard module)

Code:
 Sub AddRecord(ByVal sh As Object, ByVal Form As Object)    
      Dim lngWriteRow As Long
    
    lngWriteRow = sh.Cells(sh.Rows.Count, 2).End(xlUp).Offset(1, 0).Row
    If lngWriteRow < 2 Then lngWriteRow = 2
    
    sh.Range("B" & lngWriteRow) = Form.cbtype.Value
    sh.Range("C" & lngWriteRow) = Form.tbinitiator.Value
    sh.Range("F" & lngWriteRow) = Form.tbdescription.Value
    sh.Range("D" & lngWriteRow) = IIf(Form.CheckBox1.Value, Now(), Form.tbstart.Value)
    sh.Range("E" & lngWriteRow) = IIf(Form.CheckBox2.Value, Now(), Form.tbend.Value)


End Sub

This assumes that the selected value in your combobox (Me.cbtype) matches an existing sheet in your workbook and that the data ranges are the same for both sheets.

Hope Helpful

Dave
 
Upvote 0

Forum statistics

Threads
1,213,537
Messages
6,114,216
Members
448,554
Latest member
Gleisner2

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