Solved Move Data from One WKS to Another using multiple criteria

Microsoft corporation Excel 2013 32/64-b...
June 20, 2015 at 20:26:00
Specs: Windows 8.1
I am attempting to move data from one worksheet to another using multiple options in one row. For example, I have 3 columns that must meet certain criteria before the row can be moved to an existing sheet on the same workbook.

Column E (6) must show A, Column F (7) must show F, C or D and Column G (8) must show Y or N/A before the row can be moved to the existing worksheet and removed from the original worksheet. I've searched several different sites and was able to get the row to move using criteria from one column but I cannot figure out how to get it to recognize multiple columns and multiple options. Any assistance would be wonderful.


See More: Move Data from One WKS to Another using multiple criteria

Report •


✔ Best Answer
June 22, 2015 at 18:33:16
To add more criteria, add more logical choices. Just make sure that you include parenthesis in the proper locations so that your logic make sense.

This will be TRUE for Tom AND Mark AND either Bill OR Steve OR Fred

Tom AND (Bill OR Steve OR Fred) AND Mark

'Determine if all 3 cells meet Completed criteria
         If Cells(trow, 6) = "Funded" And _
            (Cells(trow, 7) = "Approved" Or _
             Cells(trow, 7) = "C" Or _
             Cells(trow, 7) = "D") And _
            Cells(trow, 8) = "Y" Then

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03



#1
June 20, 2015 at 20:46:23
Since you didn't include any code for us to work with, the best we can offer is a generic suggestion.

Assuming you are using an IF instruction in your code to determine what row to move, all you should need to do "expand" the IF instruction by adding some AND's and OR's.

We could be more specific if we knew what code is already working for you. There's no sense in us starting from scratch if we don't have to.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
June 20, 2015 at 21:16:10
You are right.. it would probably be better to work with something already in place. This is the code that I am currently working with but nothing happens when I update the columns to include the text.

 Private Sub Worksheet_Change(ByVal Target As Range)
'stop anything re-triggering this event macro
Application.EnableEvents = False

On Error GoTo ErrHnd

If Target.Column = 6 And Target.Value2 = "F" And _
If Target.Column = 7 And Target.Value2= "A, C, D" And _
If Target.Column = 8 And Target.Value2 = "Y, N/A" Then

    Dim rngCell As Range
    Dim rngDest As Range
    Dim strRowAddr As String
    
    'save target row address
    strRowAddr = Target.Address
    
    'find next row in destination worksheet
    Set rngDest = Worksheets("Completed"). _
               Range("A" & CStr(Application.Rows.Count)).End(xlUp).Offset(1, 0)

    'cut the source row & paste to destination
    Target.EntireRow.Cut Destination:=rngDest
    'remove the cut/copy range marquee
    Application.CutCopyMode = False
     'delete the source row
    Worksheets("Loans").Range(strRowAddr).EntireRow.Delete _
        Shift:=xlUp
End If
Application.EnableEvents = True
Exit Sub

'error handler
ErrHnd:
Err.Clear
Application.EnableEvents = True
End Sub

message edited by serrinjaxexcel


Report •

#3
June 21, 2015 at 05:10:22
I can't test your code right now but maybe later. For now, I'd like to make 2 suggestions:

1 - Run a quick test to ensure that events are enabled. If your code ever produced an error which caused the code to fail badly after EnableEvents was set to False, your code might not be firing.

You could just enable events in the Immediate window or you run this quick test in a different sheet. I like tests. ;-)

  Private Sub Worksheet_Change(ByVal Target As Range)
    MsgBox "Events OK!"
  End Sub

If you don't see the MsgBox when you enter a value in any cell, then Events aren't enabled.

2 - You could add a breakpoint in your existing code at the first instruction via F9. This will pause the code as soon as it fires, assuming it is actually firing. If it does fire, you could then Single Step through the code via F8 and watch what happens at each instruction.

This tutorial will explain some other debugging techniques that might help.

http://www.computing.net/howtos/sho...

Let me know if you figure it out before I get a chance to do my own tests.

message edited by DerbyDad03


Report •

Related Solutions

#4
June 21, 2015 at 05:45:36
I ran the suggested tests and everything seems to be working correctly. I did make a slight change to the code though and now I don't even get an error when a cell is changed in one of the target columns to reflect the target text.

Original....

If Target.Column = 6 And Target.Value2 = "F" And _
If Target.Column = 7 And Target.Value2= "A, C, D" And _
If Target.Column = 8 And Target.Value2 = "Y, N/A" Then

New
If Target.Column = 6 And Target.Value2 = "Funded" And _
Target.Column = 7 And Target.Value2 = "Approved, CCS, DS Rcvd" And _
Target.Column = 8 And Target.Value2 = "Y, N/A" Then

message edited by serrinjaxexcel


Report •

#5
June 21, 2015 at 11:26:54
I've tried some new code that seems to work a little better. I still can't get it to recognize more than one string of text in a given column and now it will run once and then not run again regardless if the right text is visible in all relative cells. I think it has to do with the
 Application.EnableEvents 
call but I've tried every combination and still only runs once. I actually have to shutdown excel and open it back up in order to get it to run again. Any assistance would be greatly appreciated.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Dim trow, tcol, st2row, lastrow
Dim rngCell As Range
Dim rngDest As Range
Dim strRowAddr As String

             
If Target.Count > 1 Then Exit Sub
trow = Target.Row
tcol = Target.Column
lastrow = 1000
'If tcol <> 20 And tcol <> 19 Then Exit Sub

If tcol = 6 And Target.Value = "Funded" And Target.Offset(0, 1) = "Approved" _
And Target.Offset(0, 2) = "Y" Then
Application.EnableEvents = False

strRowAddr = Target.Address

Set rngDest = Worksheets("Completed"). _
               Range("A" & CStr(Application.Rows.Count)).End(xlUp).Offset(1, 0)
               
Target.EntireRow.Cut Destination:=rngDest
    'remove the cut/copy range marquee
    Application.CutCopyMode = False
     'delete the source row
    Worksheets("Loans").Range(strRowAddr).EntireRow.Delete _
        Shift:=xlUp
               
'st2row = Sheets("Completed").Cells(Rows.Count, 1).End(xlUp).Row + 1
'Copy Data

'Range(Target.Address).EntireRow.Cut
'Sheets("Completed").Rows(st2row).PasteSpecial xlPasteFormulas
'Sheets("Completed").Cells(st2row, "AK") = Now()
'Rows(trow).Delete

' Rows(lastrow - 1).Copy
' Rows(lastrow).PasteSpecial
' Application.CutCopyMode = False
' Range("A2").Select
Application.EnableEvents = True

Exit Sub
Else
If tcol = 7 And Target.Value = "Approved" And Target.Offset(0, -1) = "Funded" And Target.Offset(0, 1) = "Y" Then
Application.EnableEvents = False

strRowAddr = Target.Address

Set rngDest = Worksheets("Completed"). _
               Range("A" & CStr(Application.Rows.Count)).End(xlUp).Offset(1, 0)
               
Target.EntireRow.Cut Destination:=rngDest
    'remove the cut/copy range marquee
    Application.CutCopyMode = False
     'delete the source row
    Worksheets("Loans").Range(strRowAddr).EntireRow.Delete _
        Shift:=xlUp

'st2row = Sheets("Completed").Cells(Rows.Count, 1).End(xlUp).Row + 1
'Copy Data

'Range(Target.Address).EntireRow.Copy
'Sheets("Completed").Rows(st2row).PasteSpecial xlPasteFormulas
'Sheets("Completed").Cells(st2row, "AK") = Now()
'Rows(trow).Delete

'Rows(lastrow - 1).Copy
'Rows(lastrow).PasteSpecial
'Application.CutCopyMode = False
'Range("A2").Select
Application.EnableEvents = True

Exit Sub
Else

If tcol = 8 And Target.Value = "Y" And Target.Offset(0, -1) = "Approved" And _
Target.Offset(0, -2) = "Funded" Then
Application.EnableEvents = False

strRowAddr = Target.Address

Set rngDest = Worksheets("Completed"). _
               Range("A" & CStr(Application.Rows.Count)).End(xlUp).Offset(1, 0)
               
Target.EntireRow.Cut Destination:=rngDest
    'remove the cut/copy range marquee
    Application.CutCopyMode = False
     'delete the source row
    Worksheets("Loans").Range(strRowAddr).EntireRow.Delete _
        Shift:=xlUp

'st2row = Sheets("Completed").Cells(Rows.Count, 1).End(xlUp).Row + 1
'Copy Data

'Range(Target.Address).EntireRow.Copy
'Sheets("Completed").Rows(st2row).PasteSpecial xlPasteFormulas
'Sheets("Completed").Cells(st2row, "AK") = Now()
'Rows(trow).Delete

'Rows(lastrow - 1).Copy
'Rows(lastrow).PasteSpecial
'Application.CutCopyMode = False
'Range("A2").Select
Application.EnableEvents = True

Exit Sub
Else

If tcol = 7 And Target.Value = "Cancelled" Then
Application.EnableEvents = False

strRowAddr = Target.Address

Set rngDest = Worksheets("Cancelled"). _
               Range("A" & CStr(Application.Rows.Count)).End(xlUp).Offset(1, 0)
               
Target.EntireRow.Cut Destination:=rngDest
    'remove the cut/copy range marquee
    Application.CutCopyMode = False
     'delete the source row
    Worksheets("Loans").Range(strRowAddr).EntireRow.Delete _
        Shift:=xlUp
'st2row = Sheets("Cancelled").Cells(Rows.Count, 1).End(xlUp).Row + 1
'Copy Data
'Range(Target.Address).EntireRow.Copy
'Sheets("CANCELLED").Cells(st2row, 1).PasteSpecial xlPasteFormulas
'Rows(trow).Delete

'Rows(lastrow - 1).Copy
'Rows(lastrow).PasteSpecial
'Application.CutCopyMode = False
'Range("A2").Select
Application.EnableEvents = True

Exit Sub
End If
End If
End If
End If

End Sub


Report •

#6
June 21, 2015 at 16:34:50
OK, since you are no longer using the code that you posted in Response 2 or 4, we don't need to spend much time on it, other than to say it was never going give you the results you wanted. There are far too many things wrong with it for it to work.

Keep in mind that Worksheet_Change fires at each manual change of a cell in the worksheet. Therefore, there is only one "Target": The cell that was changed.

If Target.Column = 6 And Target.Value2 = "F" And _
If Target.Column = 7 And Target.Value2= "A, C, D" And _
If Target.Column = 8 And Target.Value2 = "Y, N/A" Then

There is no way for the Target.Column to be 6 And 7 And 8 at any one time and there is no way for the Target.Value to be more than one value at any one time.

In addition, this If is incorrect for your "OR" criteria

If Target.Value2 = "A, C, D" 

This instruction is telling VBA that the Target cell must contain the exact text string contained within the parenthesis, specifically: A, C, D. It is not telling VBA to look for an A or a C or a D, it is telling VBA to look for an A followed by a comma followed by a space followed by a C followed by a comma followed by a space followed by a D.


OK, so now let's look at your latest piece of code.

You are correct when you say:

I think it has to do with the Application.EnableEvents

The very first instruction of your code, even before the Dim statement is:

Application.EnableEvents = False

Therefore, as soon as you make any change, Events are Disabled. Then, as your If instructions are processed, should any of the criteria turn out to be False, the code simply drops down to the End If's and the Sub ends, leaving Events disabled. Since there is no need to disable Events until the code is going to make a change to the sheets, there is no need to have that instruction as the first thing the code does.

Finally, once that instruction is removed, the rest of your code seems to do what it needs to do, but it is very inefficient and somewhat bloated. There is no need to independently check each column (6 then 7 then 8) for a change and then repeat essentially the same Copy/Paste code multiple times.

All you need to do is determine if the change was made to Column 6 or 7 or 8, then run the rest of your code if any one of those three conditions is True.

A more efficient version of your code is shown below. I added a few more comments and I also changed the way to Source row is deleted. I commented out the original method but left it in the macro just so you can see the difference. Note the lines with the ***. Feel free to delete them once you see the change I made.

Let me know how this works for you.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim trow, tcol
    Dim rngDest As Range

'If more than one cell was changed, Exit Sub
    If Target.Count > 1 Then Exit Sub

'Initialize variables
    trow = Target.Row
    tcol = Target.Column

'Determine if change was made in Column F, G or H
    If tcol = 6 Or _
       tcol = 7 Or _
       tcol = 8 Then
         
 'Determine if all 3 cells meet Completed criteria
         If Cells(trow, 6) = "Funded" And _
            Cells(trow, 7) = "Approved" And _
            Cells(trow, 8) = "Y" Then

'Disable events
              Application.EnableEvents = False
'***
'*** Set Source Address (not really needed, just use "Target") ***
'*** See Source row deletion section below
'***           strRowAddr = Target.Address

'Set Destination, move Row
              Set rngDest = Worksheets("Completed"). _
                Range("A" & CStr(Application.Rows.Count)). _
                            End(xlUp).Offset(1, 0)
                 Target.EntireRow.Cut Destination:=rngDest

'Remove the cut/copy range marquee
                Application.CutCopyMode = False

'Delete the source row
                Target.EntireRow.Delete Shift:=xlUp
                
'***             Worksheets("Loans").Range(strRowAddr).EntireRow.Delete _
                     shift:=xlUp

         Else

'Determine if Transaction is Cancelled
           If Target = "Cancelled" Then
              Application.EnableEvents = False

'Set Destination, move Row
            Set rngDest = Worksheets("Cancelled"). _
               Range("A" & CStr(Application.Rows.Count)). _
                           End(xlUp).Offset(1, 0)
                Target.EntireRow.Cut Destination:=rngDest

'remove the cut/copy range marquee
    Application.CutCopyMode = False

'delete the source row
                Target.EntireRow.Delete Shift:=xlUp

           End If
        End If

'Enable Events
      Application.EnableEvents = True
    End If

End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#7
June 21, 2015 at 19:51:52
DerbyDad03... this works quite well though I would like to add additional criteria to the completed cells. Is it possible for Column 7 to have 3 possible items? For instance, it is considered completed if it also shows C or D. I also changed it from a cut to a copy on the move. I noticed that with cut it would sometime not shift up properly or it would cut and never move the row.

Btw... thanks for the explanations on the why's for each change. I'm just learning VBA and appreciate the instruction.


Report •

#8
June 21, 2015 at 20:27:42
I can test it again tomorrow, but I have to say that I did not experience any of the issues you mentioned with the Cuts. In fact, I can't even think of a reason why it would behave as you mentioned, especially in an intermittent manner. The instructions should either execute the same way every time or never at all. Of course, I'm not working with your data, so it's hard to say what's going on.

As far as adding additional criteria, I guess anything is possible. My only concern is the leaking out of criteria in bits and pieces. We see it all the time in these forums.

"I need some code that does this."

"Ok, thanks for that, now can we add this"?

"Great, one more thing...can we make it do this?"

By the time we are done, we either have bloated code with all sorts of bolt-ons or we have to scrap all of the previous work and start over. I understand the desire to start with a simple question, but sometimes it better to just lay it all out there so everyone knows what the final result should look like.

Is there anything else besides wanting multiple criteria for column 7?

BTW Are the additional choices for column 7 going to result in a move to the Completed Sheet or the Cancelled sheet?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#9
June 21, 2015 at 20:55:57
I completely understand that this could turn into something quite monstrous but believe me, my only intention is to have my report recognize the additional criteria. I figure if we can get column 7 to recognize 3 different items, then getting column 8 to recognize 2 should be possible as well. And the additional choices would result in a move to the completed sheet. The code for the cancelled sheet works quite well and doesn't need any additional functionality as it serves its intended purpose.

Report •

#10
June 22, 2015 at 18:33:16
✔ Best Answer
To add more criteria, add more logical choices. Just make sure that you include parenthesis in the proper locations so that your logic make sense.

This will be TRUE for Tom AND Mark AND either Bill OR Steve OR Fred

Tom AND (Bill OR Steve OR Fred) AND Mark

'Determine if all 3 cells meet Completed criteria
         If Cells(trow, 6) = "Funded" And _
            (Cells(trow, 7) = "Approved" Or _
             Cells(trow, 7) = "C" Or _
             Cells(trow, 7) = "D") And _
            Cells(trow, 8) = "Y" Then

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#11
June 22, 2015 at 19:13:06
Perfect. This does exactly what I need and I was able to add the same functionality to column 8.

I appreciate all of your help with this.


Report •


Ask Question