PDA

View Full Version : How to copy rows from a sheet to ather sheets in the same workboouk with criteria



vag67
03-27-2017, 07:17 AM
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
03-29-2017, 08:44 PM
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
03-30-2017, 10:28 AM
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
03-30-2017, 11:52 AM
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
03-30-2017, 12:33 PM
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
03-30-2017, 12:44 PM
LOL ... thanks.

Glad to help.

Cheers

:hi: