Move row to different table depending on cell value

Hello everyone,

Added workbook has an example added.

There are two tables on this workbook: Rewatching and Rewatched.
I want to move entire rows, plus the comments with pictures added, from Rewatching to Rewatched if the cell in column G in the table Rewatching = "Completed".
The value in column G is updated with a formula.

In the added workbook row 9 should be moved to Rewatched, as the value in G9 = "Completed".

The code should also check whether or not the title in column A is already present. If it is, it only has to update the value in the corresponding row in column L + 1.
For example; Row 11. Death Note, G11 is "Completed" but Death Note is already present in the Rewatched table. So L3 should be updated +1.

In both cases, delete the target row in the Rewatching table.

Kind regards,

Marco

Hi Marco

Try the code in the attached. Let me know of issues.

Hello John,

When I run the code, it does update the "Times re-watched" field +1 for double entries.

It also creates a new column for the Rewatching table called "Column1", filled with "X" for some rows. Why does this happen?
Also, the code reports: Methode Range of object_Global has failed.

Please Login or Register  to view this content.

Kind regards,

Marco

Hi Marco

Working with Tables is different than working with data is a normal worksheet. I'm not expert on many things in VBA but Tables are one of my many shortcomings. Regarding this

It also creates a new column for the Rewatching table called "Column1", filled with "X" for some rows. Why does this happen? What does it exactly do?

I'll explain that as soon as I figure out this as I thought it did just that

When a title is already present, it updates the value in Column L + 1. But it should also delete the original
row in the Rewatching table after this.

I'll get back to you...probably tomorrow. Keep this in your pocket

I had to change the column name in the code to the Dutch version

because you'll need to do that also with any updated code.

Hi Marco

This was my first thought when I read your original request

is it possible for this code to run as a Worksheet_Change event

I'd think that's the logical way for this to run and I'll pursue that avenue if/when we get the procedure to do as you require. As I mentioned, Tables are a different animal...baby steps first...get those working, we can explore bigger steps.

Certainly...be happy to do this

may I request for you to add notes for me to understand the code a little better

I need to understand what's not working. You say this

On another note, the code works, although one thing should be different. When a title is already present, it updates the value in Column L + 1. But it should also delete the original
row in the Rewatching table after this.

I thought the code was doing that...I'l look at the test file again and address this. In the meantime...is there anything else that's NOT working?

Hi Marco

Please post the code (or a workbook that includes the code) that includes this

Please Login or Register  to view this content.

I'd like for us to be on the same page.

Hello John,

Of course. Below is the complete code. The part for alphabetical order is added on the bottom.
Be aware that for the example sheet added in my previous post, this wasn't added.
So I added another sheet with examples as attachment in this post, with below code added.
It's easier to use this sheet because my example Tables had the alphabetical order code being used on.
Be aware of the "Kolom1" in both the code and the sheet! Should be "Column1" for you.

Please Login or Register  to view this content.

Kind regards,

Marco

[REMOVED]Sorry for this post, was not supposed to reply here, but I have multiple tabs open...

This post is totally clarified for deletion...

Hi Marco
What's your actual process in entering data in Rewatching. For example Column F (Left) becomes 0 by formula (Column D minus Column E). Then Column G (Status) becomes "Completed" when Column H (Status 2) is changed to "Finished Airing".

So, it appears to me that the Change Event code will be fired by a change in Column H (Status 2). Is my thinking correct?

Hello John,

Do you want to know this for creating a Change Event code for moving the rows?
You are right, but only if Column H doesn't have the value "Finished Airing" already.
If it already has the value "Finished Airing", the Change Event code will be fired by a change in Column E (Watched Episodes). When this equals the number in Column D (Total Episodes), Column G (Status) will turn to "Completed".

Kind regards,

Marco

Hi Marco
This code is in the attached and appears to do as you require...let me know of issues.

Please Login or Register  to view this content.

Hi Marco
You have the Sort Code from a previous Thread. Try to work it in...if you have issues with that let me know.
This I'll look at (unless you want to fix it)

the colour doesn't change anymore to the same as the titles in the "Rewatched" table (light blue).

Hi Marco
To fix this

the colour doesn't change anymore to the same as the titles in the "Rewatched" table (light blue).

add this line of code

Please Login or Register  to view this content.

here

Please Login or Register  to view this content.

Originally Posted by jaslake

Hi Marco
You have the Sort Code from a previous Thread. Try to work it in...if you have issues with that let me know.
This I'll look at (unless you want to fix it)

Hello John,

I'll try to fix both these things myself.

I added an example sheet. I had to put it in a .Zip file for some reason. Wouldn't upload as a normal .xls file.
Quick explanation:
This is My Anime list in which I keep track of all my anime shows I watch.

MyAnimeList Sheet: This contains a table where all my anime shows are in. On the bottom is a button which can be used to add a new anime to the list.
If you select a show in column A it shows a picture. There are only four entries now though, instead of 200+ on the first sheet.

Rewatching Sheet: This sheet contains shows I currently rewatch. On the bottom is a button which shows the list from the MyAnimeList sheet. This is where your code comes in from this thread:
http://www.excelforum.com/excel-prog...o-another.html

It used to work, but now because of the Change Event code, it moves the row instantly to the Rewatched table, so it can't add more data.
The button tries to add the data to the Rewatching table.

Kind regards,

Marco

Oh.. haha it seems I was too late!

Thanks for the fix! I'm curious about why above problem happens....

Kind regards,

Marco

Hi Marco
The code in Module4 "Find_AnimeRewatch" fires the Change Event code because it CHANGES data in Columns 5 & 8 of Rewatching Table. Add this line of code at the top of "Find_AnimeRewatch" (just after the Dim Statements)

Please Login or Register  to view this content.

and add this line of code at the bottom of "Find_AnimeRewatch" (just before End Sub)

Please Login or Register  to view this content.

See if that works for you.

Hi Marco...one point (so you don't get feedback from the Administrators)...please don't quote entire posts...quote only relevant data...Administrators get a bit exercised about that.

Hi Marco
You're welcome...glad I could help.