Categorize items by status of completed, open

Hi

I have a workbook where by Rows 3,7, 50, 86, 126 and 254 are created as cascading cells when I double click. I could use some assistance with the VBA to be able to push the data by status for “open” to the top rows by earliest date followed by the “completed” items to the below rows within each year cascading group. I think we could use the first row in each cascading group as a data entry row. I have input a sample on the worksheet on expected results. Hope I can get some help on this. Will greatly appreciate it.

Hi Mmehmetcik,

Thank you very much for your help on working on this code.I need advice on 3 things. There seem to be a debug error on the VBA. I've attached the file with the pasted code. Can i also check on the VBA code, the status sorted based on the values of "completed' and "opened" are not on the code. Can i know where are the values represented. My last enquiry would be after i data entry in each cascading group will the rows get automatically sorted or will i need to key in the first row of each cascading group to automate the sorting?

Hi mehmetcik,

I seem to be having a debug error with the second code on the same below line. Can i ask if i am missing out something?

Please Login or Register  to view this content.

Hi mehmetcik

I have reattempted by deleting the previous code and pasting the code from message 5 as attached on sheet1. It's not functioning and the collapsible function is not working as well. Could i bother you abit to take a look at the file to see if i have done the correct steps. Thanks!!

Hi mehmetcik,

Sorry I don't get it. I can't find any comments on the code or on the file. Which cell are we referring to? On your attachment for testbook2 I faced the same issue again. When i double click on the collapsible cells in rows 3, 7, 50, 86, 126, 254, i encounter an error message on this particular line of code. The error reads as object doesn't support this property or method and points to the code after i debug. I'm sure not doing something correct. Please assist to advice.

Please Login or Register  to view this content.

Hmm thats the thing. I did not change anything. I just clicked your excel sheet testbook2 and when i clicked the collapsible rows to expand, the error message auto prompts. I have tried copying the vba and module exactly into a new worksheet with the sheet still name "sheet1" and created the collapsible rows in Rows 3,7, 50, 86, 126 and 254 and i faced the error on the exact same spot in the code. I am stuck at this roadblock. Capture.JPG

Hi Mehmetcik,

I have done that. I broke the link to the references to the previous workbook. i pasted the new code and the error showed up on this line. i seemed to realise that the error always happens when i double click the collapsible rows. Sry am at wits end...

Please Login or Register  to view this content.

Hi Mehmetcik,

Can i suggest would you be able to copy the vba into a brand new workbook with the same number of columns. I can try to see if that works when i expand the rows and the data auto sorts.

No other macros running. just this one workbook is opened. I've opened and closed it. faced the error again. Sry man.

Ok i pasted the first code and it worked for collapsing and expanding the rows. No errors encountered. The sorting did not happen.
I removed the first code and pasted the second code and the same error occurred when i clicked on the collectible rows. I have attached the file with the first code pasted on it.

Hi Mehmetcik

Which code you are referring to? The first one or the second one in post 17?

Hi Mehmetcik

I have pasted the code in post#19. When I collasped the group of 2011 there was no error but when i expanded the error happened on this line. No sorting was done as well.

Please Login or Register  to view this content.

Hi Mehmetcik. Yes it is still called sheet1. No change on that. Is there any other way we can amend the vba to achieve this intended result with the expandable rows?

Hi Mehmetcik

Apologies to be away for a while. this worked like u mentioned when rows 8 to 49 are visible. Can i check how do i make the same sort function work for rows thereafter row 49.