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.

 

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

 

[Joe4]

Are you familiar with the new FILTER function?
It is TOTALLY dynamic, and automatically spills into other rows and columns, so you only need to put the formula in the top left-corner cell and it dynamically populates the data and any changes you make to the main sheet will automatically be reflected on all the other sheets using the FILTER functions.

If that does not work for you, please explain what this does not do that you need it to do, as we would need to know that for any VBA code creation anyway.

 

[LeeLee17]

No, I am not familiar with the new Filter function...