Automatic (no button) copy and paste

[K_Stevs1]

Hi is there a way to cut and paste a row from table "Open" to the next empty row in table "Closed"?

Based on the response in cell AH = "Open" or "Closed"?

Thanks

 

[Joe4]

How/when is the value in column AH being updated?
You can use VBA to automatically move that row if column AH is being updated manually.

Another option is to have one big table with ALL your records.
Then, you can use the new FILTER function (not to be confused with the old filtering capabilities) to create two lists dynamically, one of all "Open" records and one of all "Closed" records.
See here for examples of how to use this new function: FILTER Function

 

[K_Stevs1]

How/when is the value in column AH being updated?
You can use VBA to automatically move that row if column AH is being updated manually.

Another option is to have one big table with ALL your records.
Then, you can use the new FILTER function (not to be confused with the old filtering capabilities) to create two lists dynamically, one of all "Open" records and one of all "Closed" records.
See here for examples of how to use this new function: FILTER Function

Hi thank you for responding,

The AH cell, is a drop down and is updated manually, by the user.

I have considered the filter option by think it will be come too bulk due to the possible amount of data.

I have tried looking at the VBA code, but struggling to find one that will work in the way I need, without a button.

many thanks

 

[Joe4]

I have considered the filter option by think it will be come too bulk due to the possible amount of data.

How much data are we talking about exactly?
Did you look at the old filtering functionality, or the new Filter function?
The new Filter function is a "Spill" function, meaning you only have to enter the formula in one cell, and it automatically returns all rows meeting your criteria.

I have tried looking at the VBA code, but struggling to find one that will work in the way I need, without a button.

You can use a Worksheet_Change event procedure, which is VBA code that runs automatically when a particular cell is automatically updated (no need for buttons).
The only question is where all this data resides. Are you dealing with two different sheets?
What is the name of the sheet your main data is on, and what is the name of the sheet you want to move the data to?

Or are you really dealing with actual "Excel tables"?
If so, those can be a pain to work with because you will need to insert new table rows to move the data into, I believe.

 

[K_Stevs1]

How much data are we talking about exactly?
Did you look at the old filtering functionality, or the new Filter function?
The new Filter function is a "Spill" function, meaning you only have to enter the formula in one cell, and it automatically returns all rows meeting your criteria.


You can use a Worksheet_Change event procedure, which is VBA code that runs automatically when a particular cell is automatically updated (no need for buttons).
The only question is where all this data resides. Are you dealing with two different sheets?
What is the name of the sheet your main data is on, and what is the name of the sheet you want to move the data to?

Or are you really dealing with actual "Excel tables"?
If so, those can be a pain to work with because you will need to insert new table rows to move the data into, I believe.

Hi.

the amount of data rows can very, but I wouldn't expect more than 100 in total per year, I have looked at the newer filtering approach but some of the users are really not IT/Excel savvy and would probably forget to do the filter.

I will have a look at the worksheet_change event procedure as that sounds more of what I am looking to do. There will be two worksheets (within the same workbook) one is titled Open the other Closed, and each one will be the table to match the sheet title.

These are based on the data going into actual excel tables.

Thanks

 

[Joe4]

the amount of data rows can very, but I wouldn't expect more than 100 in total per year, I have looked at the newer filtering approach but some of the users are really not IT/Excel savvy and would probably forget to do the filter.

That is not very much data. It should not present any problems to work with.

Regarding your users, that is just the thing - they shouldn't need to do a single thing!
You just set up the FILTER formula in the first cell of your sheet, and it will automatically return ALL records meeting the criteria.
And it is dynamic! As data is updated in the main table, the results returned by the function will automatically be updated.
It really sounds like it will do all that you need, and does not require any VBA code.
I highly recommend checking this out first.

I would recommend having three sheets:
1. Main data
2. Open data
3. Closed data

So the Main data sheets is the sheet that contains all your data, and is where you would make the updates.
The Open data and Closed data sheets simply have two things in them:
1. The header row
2. A single FILTER formula in the first cell under the header row that returns all the data meeting the criteria (Open or Closed)

Then as you make updates to the Main data sheet, the data shown on the Open and Closed sheets will be automatically updated.

This new function is really slick and makes these sort of things much easier than they ever were before.

I love using VBA, but there is no reason to create VBA to do something that already exists!

 

[K_Stevs1]

Thank you I will try the filtering way and ask them how they fell about it, before looking more into the vba.

Thanks again for your help

That is not very much data. It should not present any problems to work with.

Regarding your users, that is just the thing - they shouldn't need to do a single thing!
You just set up the FILTER formula in the first cell of your sheet, and it will automatically return ALL records meeting the criteria.
And it is dynamic! As data is updated in the main table, the results returned by the function will automatically be updated.
It really sounds like it will do all that you need, and does not require any VBA code.
I highly recommend checking this out first.

I would recommend having three sheets:
1. Main data
2. Open data
3. Closed data

So the Main data sheets is the sheet that contains all your data, and is where you would make the updates.
The Open data and Closed data sheets simply have two things in them:
1. The header row
2. A single FILTER formula in the first cell under the header row that returns all the data meeting the criteria (Open or Closed)

Then as you make updates to the Main data sheet, the data shown on the Open and Closed sheets will be automatically updated.

This new function is really slick and makes these sort of things much easier than they ever were before.

I love using VBA, but there is no reason to create VBA to do something that already exists!