Help On Transfer Data from One sheet to sheet 2 when condition met vba

**VISHALS** · 2011-09-24, 11:19 AM

Hi All,

its the first time am submitting to this forum and am glad to be a member.

I needed help on how can i clear contents of a row when a condition is met in sheet 1 and copy same to sheet2 as a records.

e.g when order status is CLOSE then it shall copy the data to sheet 2 as a record and clear the content on sheet 1.

Thanks to find attached file .

Many thanks in advance for help.

Regards,

Vishals

**Ken Puls** · 2011-09-26, 06:39 PM

Hi Vishals, and welcome to the forum!

This macro will do what you're asking, but I have a bit of a problem in it too. To use this

Open the VBE (press Alt+F11)
Browse to your workbook
Open the "Sheet1" module
Paste in the following code:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    'Check if value = "CLOSE"
    If Target.Value = "CLOSE" Then
        'Turn off events to prevent recursive calls
        Application.EnableEvents = False
        'Copy row to new worksheet
        Target.EntireRow.Copy
        With Worksheets("Sheet2")
            .Range("A" & .Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteAll
        End With
        'Delete the source row
        Target.EntireRow.Delete
        'Turn events back on
        Application.EnableEvents = True
    End If
End Sub

Exit the VBE

At this point, every time you change a value to "CLOSE", it will copy it to the next available row on Sheet2.

The issue, though, is that you're using a formula to work out your quantity. So we need to figure out how your quantities are going to be retained so that we can fix this...

**VISHALS** · 2011-09-27, 06:44 AM

Hi Ken,

Many thanks for the reply i try it , it work perfect . Concerning the qty its will not be retained as formula as all are done by vba for the data to be procress. just a question by curiosity if i would like to put the code rather than in the worksheet change event but in a sub shall i still use the target. value or different way of addressing.

as i don't want it to be in the worksheet event but rather in a sub that i can call as there in reality the data shall be saved in another workbook as a record book

many thanks again for your precious time and help.

**Ken Puls** · 2011-09-27, 06:54 AM

Sure, no problem. Try this in a standard module instead. It has the same issues as the routine above, but will let you fire in manually.

Code:

Private Sub TransferMe()
Dim cl As Range
    'Make sure only one cell selected
    Set cl = Selection
    If cl.Cells.Count > 1 Then
        MsgBox "Please select a single cell only!", vbOKOnly + vbCritical
        Exit Sub
    End If
    
    'Check if value = "CLOSE"
    If cl.Value = "CLOSE" Then
        
        'Copy row to new worksheet
        cl.EntireRow.Copy
        With Worksheets("Sheet2")
            .Range("A" & .Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteAll
        End With
        
        'Delete the source row
        cl.EntireRow.Delete
    End If
End Sub

**VISHALS** · 2011-09-27, 08:25 AM

Hi Ken,

Thanks a lot its ok . by the way how do i mark the thread solved?

thanks for the reply.

**VISHALS** · 2011-09-27, 02:52 PM

Hi Ken,

I just needed some more help on that.

The code for copy, paste and delete is working correctly right now.

i have added two more columns one is pack details and vessel name. so everytime the user select to close an order it should check if the pack details is entered and that the vessel name has been entered and if these conditions is not respected it shall pop up a msg advising the user which order number that the details is missing for example as such :

if order 1007 , user select close. msgbox to give : Order number 1007 pack details not input and vessel name missing, cannot proceed.

and then it clear the CLOSE status place for that specific order.

I have been trying to put some of the code put its not working.
Please see attached sheet for details.

Many thanks for helping or giving a hint on how i can do that.

**Ken Puls** · 2011-09-27, 06:25 PM

I'd modify your Worksheet_Change event to read as follows:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    'Check if value = "CLOSE"
    If Target.Value = "CLOSE" Then
        'Turn off events to prevent recursive calls
        Application.EnableEvents = False
        'Copy row to new worksheet
       
        If check_condition_before(Target) = False Then
            MsgBox "Sorry, but you need to complete the Pack Details and Vessel Name!", vbOKOnly + vbCritical
            Target.ClearContents
        End If
        
        Application.EnableEvents = True
    End If
End Sub

And I'd turn your Check_Condition_Before into a function as follows:

Code:

Function check_condition_before(rngTarget As Range) As Boolean
Set rngvalidate = Range("E" & rngTarget.Row & ":F" & rngTarget.Row)
If Application.WorksheetFunction.CountA(rngvalidate) = 2 Then
    check_condition_before = True
Else
    check_condition_before = False
End If
End Function

**VISHALS** · 2011-09-28, 06:41 AM

Hi Ken,

Thanks again for your precious time. its working correctly.

can we break the conditions as such :

if only packing is missing then we have a msg of : " Missing packing details"
if only vessel name is missing then we have a msg of : " missing vessel name"

and if both then we have the msg :

Sorry, but you need to complete both the Pack Details and Vessel Name!

thanks advise if this is possible.

**Ken Puls** · 2011-09-28, 06:39 PM

Absolutely possible.

Code modifications:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sErrorMsg As String
    'Check if value = "CLOSE"
    If Target.Value = "CLOSE" Then
        'Turn off events to prevent recursive calls
        Application.EnableEvents = False
        'Copy row to new worksheet
        Select Case check_condition_before(Target)
            Case Is = "Missing-Vessel"
                sErrorMsg = "the vessel name"
            Case Is = "Missing-Pack"
                sErrorMsg = "the pack details"
            Case Is = "Missing-All"
                sErrorMsg = "both the vessell name and pack details"
        End Select
    End If
    
    If sErrorMsg = vbNullString Then
        'do nothing as all is okay
    Else
        MsgBox "Sorry, but you are missing " & sErrorMsg & "!", vbOKOnly + vbCritical
        Target.ClearContents
    End If
    
    Application.EnableEvents = True
End Sub

Code:

Function check_condition_before(rngTarget As Range) As String
    Set rngvalidate = Range("E" & rngTarget.Row & ":F" & rngTarget.Row)
    Select Case Application.WorksheetFunction.CountA(rngvalidate)
        Case Is = 2
            check_condition_before = "Valid"
        Case Is = 1
            If Range("E" & rngTarget.Row).Value = vbNullString Then
                check_condition_before = "Missing-Pack"
            Else
                check_condition_before = "Missing-Vessel"
            End If
        Case Else
            check_condition_before = "Missing-All"
    End Select
End Function