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.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Move expanding selection of cells to new tabs based on condition
- Thread starter LeeLee17
- Start date
Joe4
MrExcel MVP, Junior Admin
- Joined
- Aug 1, 2002
- Messages
- 72,441
- Office Version
- 365
- Platform
- Windows
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.
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.
Upvote
0
Joe4
MrExcel MVP, Junior Admin
- Joined
- Aug 1, 2002
- Messages
- 72,441
- Office Version
- 365
- Platform
- Windows
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!).
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!).
Upvote
0
Excel Facts
Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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.
Upvote
0
Joe4
MrExcel MVP, Junior Admin
- Joined
- Aug 1, 2002
- Messages
- 72,441
- Office Version
- 365
- Platform
- Windows
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.
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.
Upvote
0
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...
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...
Upvote
0
Joe4
MrExcel MVP, Junior Admin
- Joined
- Aug 1, 2002
- Messages
- 72,441
- Office Version
- 365
- Platform
- Windows
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 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?
Upvote
0
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
Upvote
0
