Cut and paste an entire row to another sheet if a cerain criteria is met

[julia_helena]

Hi!
this forum is always my first source of information when I'm looking for help in excel questions. And in doing so I was able to solve a lot of problems but now, even after reading a lot of threads I don't get it. I tried and tried, but it won't work.
I have a file with information about our collection. What I want to do is that when we are moving on to another season, the "old product" should be moved to another sheet.
When the "seasons column" on a sheet called "DetailedData" is changing to the next season, the entire row should be moved to a sheet called "archive" (when FallWinter16 (=FW16) is moving to FW17, SpringSummer16 (=SS16) is moving to SS17, the "old" products should move to the next sheet).
Could I ask for help? I'm also happy to send you the document if this makes things easier.
Thank you very much for your patience and for reading this!
Julia

[Mumps1]

Hi Julia. If you could post your file it would certainly help.

[julia_helena]

Here's the document. Thanks a lot for having a look at it!

[Mumps1]

Hi Julia. After looking at your file, I noticed that the "Season" column (C) had 'FW16" and 'AS16' but no 'SS16'. If 'FW16' is changed to 'FW17' you want that row moved to the "Archive" sheet. By "moved" can I assume that you want to copy it to the "Archive" sheet and deleted from the "DetailedData" sheet? Then you want to do the same with 'SS16' although there were no occurrences of this in column C. Do you want to do the same with 'AS16'?

[julia_helena]

Hi! You are perfectly right. The file that I have attached currently contains only the FW season but will contain also SS seasons in the future. What I would like is as you put it correctly that regardlesss which season (FW, AS or SS) the entry will be copied to the Archive sheet and deleted from the detailed data sheet as soon as we move to another year.
16, then 17, later 18, etc.
Thank you so much for your patience!
Julia

[Mumps1]

Hi Julia. I noticed that when you ungroup the rows starting at row 25, that there are 6 blank lines between each entry in column C. Will these now empty cells in column C have FW, AS or SS season in them at any point? If they will, do you want to move them to 'Archive' as well?

[julia_helena]

yes, they will have a status in them as well. The six lines under each entry somehow belong to this entry. The "main" entry is the product and the lines underneath are the colors. also the colors will get a FW,SS or AS status with a regarding year, but are still somehow linked to the product above. I am so thankful that you're setting aside time for that!

[Mumps1]

Hi Julia. I have been playing with your file to see how your data is organized. In column C you have a formula which returns the value in column G plus the last 2 figures of column H. I assume then that when you want to move to the next year, you change the value in column H and the value in column C adjusts automatically. Do you change the values in column H one at a time or do you change the whole of column H at once? I'm sorry for all these questions but the answers will determine how the macro will work.

[julia_helena]

Hi Mumps1, yes that's correct, I'd change the year in column H which would change the value in column C. I would like to change the entries one at a time nad not the whole column at once. You can ask as many questions as you want! Again I'm just thankful! You shouldn't be the one apologizing!

[Mumps1]

Hi Julia. I have been struggling with the file. Please open the attached file. I deleted column A in the "Archive" and "DetailedData" sheets just to make coding easier. If you change the date in column G, that row will be copied to the "Archive" sheet. This part worked properly. When I included the line of code to delete the copied row from the "DetailedData" sheet, it started giving me all sorts of problems and error messages and I don't know why. I have used this approach many times before without any problems. I suspect it might be due to the grouping of the rows but I'm not sure. In the macro, which you can find in the worksheet code module, I have commented out the line to delete the row. You can use it as is and the copying will be done properly. I'm sure that there are VBA experts on the forum who may have a solution to this problem. Could I suggest that you start another thread explaining the problem that it works properly unless you include the line of code that deletes the row. Perhaps post a copy of the file with the macro included. If you find a solution, I would appreciate it if you sent me a private message with a link to the thread. I would be very interested in having a look. Many thanks.