How to copy rows from a sheet to ather sheets in the same workboouk with criteria

[vag67]

Hi my friends I'm looking fora Userform option that allows from Database called Sheet Export updated and atthe same time, carry through the information to the required oil type sheet.
For examble when I insert a new data (Row) in the EXPORT sheet and the criteria in column D is UNLEADED then the new row to copied to the reuired type sheet UNLEADED . I have uploaded an examble file
I ask for your help please

[Logit]

This is one way to accomplish your goal :

Option Explicit


Private Sub btnCancel_Click()
    Unload Me
End Sub


Private Sub btnOK_Click()
    Dim ws As Worksheet
    Set ws = Worksheets("EXPORT")
    Dim newRow As Long
    newRow = Application.WorksheetFunction.CountA(ws.Range("A:A")) + 1
    ws.Cells(newRow, 1).Value = Me.txtAa.Value
    ws.Cells(newRow, 2).Value = Me.txtVessel.Value
    ws.Cells(newRow, 3).Value = Me.txtTK.Value
    ws.Cells(newRow, 4).Value = Me.txtProduct.Value
    ws.Cells(newRow, 5).Value = Me.txtEnapeh.Value
    ws.Cells(newRow, 6).Value = Me.txtAheh.Value
    ws.Cells(newRow, 7).Value = Me.txtDestination.Value
    ws.Cells(newRow, 8).Value = Me.txtLit.Value
    ws.Cells(newRow, 9).Value = Me.txtKg.Value
    ws.Cells(newRow, 10).Value = Me.txtDensity.Value
    ws.Cells(newRow, 11).Value = Me.txtLit2.Value
    ws.Cells(newRow, 12).Value = Me.txtKg2.Value
    ws.Cells(newRow, 13).Value = Me.txtVef.Value
    ws.Cells(newRow, 14).Value = Me.txtShipVef.Value
    
    If Me.txtProduct.Value = "UNLEADED" Then
        WriteToSheetUnleaded
    End If
    clrForm
    Me.txtAa.SetFocus
End Sub


Sub WriteToSheetUnleaded()
Dim ws As Worksheet
    Set ws = Worksheets("UNLEADED")
    Dim newRow As Long
    newRow = Application.WorksheetFunction.CountA(ws.Range("A:A")) + 1
    ws.Cells(newRow, 1).Value = Me.txtAa.Value
    ws.Cells(newRow, 2).Value = Me.txtVessel.Value
    ws.Cells(newRow, 3).Value = Me.txtTK.Value
    ws.Cells(newRow, 4).Value = Me.txtProduct.Value
    ws.Cells(newRow, 5).Value = Me.txtEnapeh.Value
    ws.Cells(newRow, 6).Value = Me.txtAheh.Value
    ws.Cells(newRow, 7).Value = Me.txtDestination.Value
    ws.Cells(newRow, 8).Value = Me.txtLit.Value
    ws.Cells(newRow, 9).Value = Me.txtKg.Value
    ws.Cells(newRow, 10).Value = Me.txtDensity.Value
    ws.Cells(newRow, 11).Value = Me.txtLit2.Value
    ws.Cells(newRow, 12).Value = Me.txtKg2.Value
    ws.Cells(newRow, 13).Value = Me.txtVef.Value
    ws.Cells(newRow, 14).Value = Me.txtShipVef.Value
    
    
End Sub


Sub btnDelete_Click()
    Selection.EntireRow.Delete
End Sub


Sub clrForm()
 Dim ctl As MSForms.Control


    For Each ctl In Me.Controls
        Select Case TypeName(ctl)
            Case "TextBox"
                ctl.Text = ""
           ' Case "CheckBox", "OptionButton", "ToggleButton"
               'ctl.Value = False
           ' Case "ComboBox", "ListBox"
                'ctl.ListIndex = -1
        End Select
    Next ctl
End Sub

[vag67]

Hi Logit
At first i want to thank you very much for your response and your valuable help.
This is exactly what i looking for . The only problem is that when i ιnsert in the EXPORT sheet a row with the product name, in the column D, like FUEL OIL or DIESEL ULSD or GASOIL 0,1 the rows are not copied to the corresponding sheets but only when the product name is UNLEADED

[Logit]

My apologies. Misunderstood your example

For examble when I insert a new data (Row) in the EXPORT sheet and the criteria in column D is UNLEADED then the new row to copied to the reuired type sheet UNLEADED . I have uploaded an examble file

Thought you were indicating only when UNLEADED was present.

Simple fix ... deleted a couple lines of code. The following works as you've indicated :

Option Explicit


Private Sub btnCancel_Click()
    Unload Me
End Sub


Private Sub btnOK_Click()
    Dim ws As Worksheet
    Set ws = Worksheets("EXPORT")
    Dim newRow As Long
    newRow = Application.WorksheetFunction.CountA(ws.Range("A:A")) + 1
    ws.Cells(newRow, 1).Value = Me.txtAa.Value
    ws.Cells(newRow, 2).Value = Me.txtVessel.Value
    ws.Cells(newRow, 3).Value = Me.txtTK.Value
    ws.Cells(newRow, 4).Value = Me.txtProduct.Value
    ws.Cells(newRow, 5).Value = Me.txtEnapeh.Value
    ws.Cells(newRow, 6).Value = Me.txtAheh.Value
    ws.Cells(newRow, 7).Value = Me.txtDestination.Value
    ws.Cells(newRow, 8).Value = Me.txtLit.Value
    ws.Cells(newRow, 9).Value = Me.txtKg.Value
    ws.Cells(newRow, 10).Value = Me.txtDensity.Value
    ws.Cells(newRow, 11).Value = Me.txtLit2.Value
    ws.Cells(newRow, 12).Value = Me.txtKg2.Value
    ws.Cells(newRow, 13).Value = Me.txtVef.Value
    ws.Cells(newRow, 14).Value = Me.txtShipVef.Value
      
    WriteToSheetUnleaded
    
    clrForm
    Me.txtAa.SetFocus
End Sub


Sub WriteToSheetUnleaded()
Dim ws As Worksheet
    Set ws = Worksheets("UNLEADED")
    Dim newRow As Long
    newRow = Application.WorksheetFunction.CountA(ws.Range("A:A")) + 1
    ws.Cells(newRow, 1).Value = Me.txtAa.Value
    ws.Cells(newRow, 2).Value = Me.txtVessel.Value
    ws.Cells(newRow, 3).Value = Me.txtTK.Value
    ws.Cells(newRow, 4).Value = Me.txtProduct.Value
    ws.Cells(newRow, 5).Value = Me.txtEnapeh.Value
    ws.Cells(newRow, 6).Value = Me.txtAheh.Value
    ws.Cells(newRow, 7).Value = Me.txtDestination.Value
    ws.Cells(newRow, 8).Value = Me.txtLit.Value
    ws.Cells(newRow, 9).Value = Me.txtKg.Value
    ws.Cells(newRow, 10).Value = Me.txtDensity.Value
    ws.Cells(newRow, 11).Value = Me.txtLit2.Value
    ws.Cells(newRow, 12).Value = Me.txtKg2.Value
    ws.Cells(newRow, 13).Value = Me.txtVef.Value
    ws.Cells(newRow, 14).Value = Me.txtShipVef.Value
    
    
End Sub


Sub btnDelete_Click()
    Selection.EntireRow.Delete
End Sub


Sub clrForm()
 Dim ctl As MSForms.Control


    For Each ctl In Me.Controls
        Select Case TypeName(ctl)
            Case "TextBox"
                ctl.Text = ""
           ' Case "CheckBox", "OptionButton", "ToggleButton"
               'ctl.Value = False
           ' Case "ComboBox", "ListBox"
                'ctl.ListIndex = -1
        End Select
    Next ctl
End Sub

[vag67]

Hi Logit .
It Works perfectly.
Ihave renamed the work book to "Logit" for remember that there is a friend some miles away from here.
Thank you very much again.

[Logit]

LOL ... thanks.

Glad to help.

Cheers