Results 1 to 10 of 10

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Attached Files Attached Files

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,091
    Articles
    79
    Blog Entries
    14
    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...
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    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.

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,091
    Articles
    79
    Blog Entries
    14
    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
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    Hi Ken,

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

    thanks for the reply.

  6. #6

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

    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.


    Attached Files Attached Files

  7. #7
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,091
    Articles
    79
    Blog Entries
    14
    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
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  8. #8
    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.

  9. #9
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,091
    Articles
    79
    Blog Entries
    14
    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
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  10. #10
    many many thanks Ken for this kind help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •