Move expanding selection of cells to new tabs based on condition

[LeeLee17]

I would appreciate an assist with vba code that moves adjacent cells from one sheet to another. The loop will be based on a DV selection: Next, None or Completed. The DV column will reside in all sheets at “A”. As the data moves through the work process move (not copy) data is collected and the number of adjacent cells grows. It will need to find the last row in the new tab to paste to. Thank you for your expertise and time.

 

[Joe4]

Check out the link I have in my first reply.
You would simply enter the formula once on each sheet, telling it which values you want to return for that particular sheet, and it will dynamically pull all the records meeting the criteria.
And as you make changes to your main sheet, it will automatically be reflected on all the other sheets.

It really is a really cool new function that allows you to automate things without having to use VBA, or having to interact with the old manual Filter and Advanced Filter features.
People sometimes confused it with those old features, but this is a new Function to be used in formulas with "spill" capabilities (meaning you enter the formula in just one cell, but it will populate multiple rows and columns).

Now maybe after reviewing it, you will determine that it doesn't quite do everything you need. If that is the case, then let us know what it doesn't do that is needed. But you should at least investigate it first, as it could greatly simplify things for you (no sense in recreating the wheel if there is already something built-in that does what you need!).

 

[LeeLee17]

Agreed, thanks for this. I'll check it out as soon as I can. Thanks again!

 

[LeeLee17]

I've watched that and another video- what it doesn't do is Move the data. It would just copy the data from tab to tab. He needs to be able to report real time status which would be accomplished by moving the data as it moves to the various work processes.

 

[Joe4]

OK, I think I finally understand what you are saying now. So it is not just moving it off of one main sheet, but rather have the functionality to be able to move it from any sheet to whatever is designated as the next sheet. Is that correct?

If so, what should "trigger" the move to happen?
Should it happen right as someone makes an update to column A?
Or should it when a certain button is pushed (I see you have buttons on your image)?
If buttons, do all the sheets have the same buttons? If so, this is going to involve a LOT of VBA code.

Note just to go back to the original idea for just one moment, my thought was to do the following:
- All data entry and changes happen on one main tab. Data is never "moved" off of this sheet, this is where the total data set resides.
- Have a separate sheet for all your different categories (like you have now), and have a Filter formula in each returning the records meeting that category criteria (i.e. all the "Open" records appear on the "Open" tab, all the "Rescind" records appear on the "Rescind" tab, etc).

So the only difference from your original ask is that all the records would always appear on your "Main" tab, and that is where you would made all the updates.
But all your other tabs would look exactly like you want them to (i.e. if you want to see all the "Open" records, just go to the "Open" tab, etc).

It is a little bit different way of thinking, but may serve your purpose, ultimate goal. If not, and you absolutely do not want all the values on same main tab, then the VBA route will be the way we have to go. We would just need that "trigger" questions answered.

 

[Joe4]

Also, I assume that all the sheets have exactly the same headers, right?
And what row are the headers in (and what row does the first line of data start on)?

 

[LeeLee17]

rather have the functionality to be able to move it from any sheet to whatever is designated as the next sheet. Is that correct? Yes
If buttons, do all the sheets have the same buttons? If so, this is going to involve a LOT of VBA code. I prefer buttons so that he has control and could send one "back" to an earlier process tab, i.e, he moves a Next Steps to No Response but then weeks go by and the client responds and wants to move forward. He can then send it "back" to the Next Steps tab. I'm aware- actually 22 distinct macros; however, if the code is broad enough I can modify it for each of the scenarios. I greatly appreciate your interest and expertise! Thank you!!

P.S. It would be tremendously helpful if you include 'comments to the actions of the code...

 

[Joe4]

So, how exactly should this work then?
If you are on the "Open" tab, and you click the "Next" button, should it look in column A and then move ALL of the rows with the word "Next" in them to the "Next" tab?

 

[Joe4]

So, how exactly should this work then?
If you are on the "Open" tab, and you click the "Next" button, should it look in column A and then move ALL of the rows with the word "Next" in them to the "Next" tab?

If these assumptions are right, below is some code that should do what you want.

There is just one thing - and that is determining where the last row of data is on the sheet that you want to paste to. The easiest way is to pick some column that will ALWAYS have data in it for any row with data. Since column A is being used to direct to move data, I am guessing that may not always have values in it. So I used column B. That can easily be changed, if there is a better column to use.

Here is the code:

Sub MoveToNext()

    Dim ws As Worksheet
    Dim lr As Long
    Dim r As Long
    Dim nr As Long
    
'   Capture active sheet
    Set ws = ActiveSheet
    
    Application.ScreenUpdating = False
    
'   Find last row in column with data
    lr = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
'   Loop through all rows in sheet backwards up to row 4
    For r = lr To 4 Step -1
'       Check to see if cell A is "Next"
        If ws.Cells(r, "A").Value = "Next" Then
'           Find next available row on "Next" sheet, using column B
            nr = Sheets("Next").Cells(Sheets("Next").Rows.Count, "B").End(xlUp).Row + 1
'           Copy row to "Next" sheet
            ws.Range(Cells(r, "A"), Cells(r, "O")).Copy Sheets("Next").Cells(nr, "A")
'           Delete row on source sheet
            ws.Rows(r).Delete
        End If
    Next r
    
    Application.ScreenUpdating = True
    
End Sub

I made this code relative to whatever sheet you are on when you run it. If you then put this code in a General module (instead of a Sheet module), it will then allow you to re-use this code for all your "Next" buttons on all your sheets (so you don't need top repeat it). If we wanted to get really clever, we could even pass in the destination sheet name as a parameter, so you could re-use this code for all your moves. You would still need a small procedure attached to each button telling it which values to pass.

 

[LeeLee17]

In the "Open" tab, you can select: Next, No Response or Rescind from the DV in column A. When you push the appropriate button (Next, No Response or Rescind) the macro moves the row- ideally staring at column B to the appropriate tab (Next, No Response or Rescind). If it is easier to name a range of cells to move - that will work. The number of cells will grow as the lead moves through the work process

 

[LeeLee17]

since column A is being used to direct to move data, I am guessing that may not always have values in it. So I used column B. That can easily be changed, if there is a better column to use.- This is interesting because theoretically there will be times when there is nothing in a particular process but the headers...