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]

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.

 

[Joe4]

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

No, I am not talking about the destination sheets. I am talking about the rows with data that are being marked to move. There would be no point in marking a blank row to be moved, right?

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. Clever is always intriguing, what other info so you need from me?

OK, here is what I came up with (and I changed it so that it only moves from column B, and copies to the last column, whatever that may be).
So, put all of this code in a General module (not in any of the sheet modules).

First, we have our "generic" move code with parameter than looks like this:

Private Sub MoveData(sh As String)
'   Pass the sheet name you want to move to as the parameter ("sh")

    Dim ws As Worksheet
    Dim lr As Long
    Dim r As Long
    Dim lc 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 matches sought out value
        If ws.Cells(r, "A").Value = sh Then
'           Find last column with data in row
            lc = ws.Cells(r, ws.Columns.Count).End(xlToLeft).Column
'           Find next available row on destination sheet, using column B
            nr = Sheets(sh).Cells(Sheets(sh).Rows.Count, "B").End(xlUp).Row + 1
'           Copy row from columns B to end to destination sheet
            ws.Range(Cells(r, 2), Cells(r, lc)).Copy Sheets(sh).Cells(nr, "B")
'           Delete row on source sheet
            ws.Rows(r).Delete
        End If
    Next r
    
    Application.ScreenUpdating = True
    
End Sub

Then, in the same procedure, we want to set up a procedure for each different tab we want to move to:
Here are the first two (the rest follow the same structure):

Sub MoveToRescind()
    Call MoveData("Rescind")
End Sub

Sub MoveToNext()
    Call MoveData("Next")
End Sub

Then, all we have to do is assign each button to the appropriate procedure.
So all that buttons that are moving to the "Rescind" sheet can be assigned to the "MoveToRescind" procedure, etc.

 

[LeeLee17]

I am looking for simple code that loops for a condition, if met it moves a range of cells to a specified tab. The condition will always be in column A. The number of cells moved increases as the work moves from process to process. I greatly appreciate your time & expertise!

 

[LeeLee17]

I should add that there will be 3 or 4 conditions: Next, No Res, Rescind, Completed. So, it will have to look for any of these and move to the appropriate named tab: Next, No Res, Rescind, Completed.

 

[Joe4]

With questions like this, it is often hard for us to visualize what you are working with from very generic statements (remember, while you are quite familiar with your data structure and desired output, we are not - all we have to go on is what little you have shared with us). You often increase the likelihood of getting assistance if you can post a sample of your existing data and show us your expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[LeeLee17]

I'm hoping this helps visulize the task:

 

[LeeLee17]

In the example above, the "1st" row is flagged Next, the end user would push the "Next" button that would send rows B-O to the next tab. The DV in column A also has No Resp & Rescind to send the same data to those tabs. Ideally, I need code that I can amend for the subsequent processes on each of the listed tabs. I appreciate the feedback- please feel free to ask questions. Thank you!

 

[Joe4]

Wouldn't it be far easier to enter all values/rows on one main sheet, and then use the new Excel FILTER function to return all "Next" records on the "Next" sheet, all "Rescind" records on the "Rescind" sheet, etc? No VBA would be needed then.

See: FILTER function - Microsoft Support

 

[LeeLee17]

Many thanks but unfortunately, no. The work is dynamic and the end user has real time limitations given the # of sales leads. I'm trying to build a "pipeline" app using vba for him. He is finding that he "loses" leads & work status in the one spreadsheet that he dumps everything into currently...