Macro has stopped working without obvious reasons.

Hello everyone.

I’ve got data with multiple rows sorted in descending order based on date in the column B.

I’ve been using macros that copies all rows with the same date to the sheet added to workbook BackTest.xslm and gives name to the sheet equal to date. For example, if I have 5 rows with date 2021.3.17, 3 rows 2021.03.10, and 12 rows with date 2021.02.16, the macros will add 3 sheets to the file BackTest.xslm with names 2021.3.17, 2021.03.10, and 2021.02.16 with rows of data copied to these sheets correspondingly. Today, for some reason this macro has stopped working properly, it creates multiple new sheets, often with dates that are not in the list, and these sheets are empty, it does not copy any data onto new sheets. I’m sure that there were no any changes made to the code, as I had several copies of the file and all of them are not working. I can’t rule out possibility that it has stopped working due to changes in Excel Options, although I could not figure out which one. I've tried to reinstall Microsoft Office with no success. As I do not exactly understand how this macro works, I can’t make amendments to it to make it work. I would be grateful for any help to restore or to modify this macro to make it work properly. Please find file with macro and sample data as an attachment to this post.

Thanks in advance.

Dilshod

Hello Roy,

Thank you very much for your help. Macro works perfectly well, and thanks a lot for explicit comments!

Couple of small requests: would you be able to change code so that it creates new sheets in the book C:\Test\BackTest.xlsm, and to change name from "long date" format to short one yyyy.mm.dd? I tried to change "long date" to "short date" format, but it did not work. I can't incorporate this macro within BackTest.xlsm file for technical reasons, as I need it to be in the separate file. Thanks again. Highly appreciate your help.

Dilshod

Quote from royUK

I'm not sure what you mean about the workbook. Do you mean that you need to new sheets to go to C:\Test\BackTest.xlsm?

Thanks for help with date format.

Yes, all these data was meant to be sorted by date and copied to the newly created sheets in another workbook namely BackTest.xlsm as it will be processed there by another macro.

Thanks in advance.

Quote from royUK

That seems strange why not have all processes in one workbook?

It will slow the macro down because it will have to check each sheet to see if it exists in the other workbook

By default there is only one sheet in the file BackTest.xlsm, "Sheet1", so frankly speaking module of the code checking existense of the sheet with the same name is redundant. Upon processing all the sheets are converted to files with the same names and are deleted from BackTest file. I already thougt of adding this macro to the same workbook, but it is not practical in my case as it will significantly complicate macro. The sample data I've sent to you is small, in reality it might be dosens if not hundreds of thousands rows of data, which can not be processed at once by the file BackTest.xlsm as it progressively becomes slower with incresed wolume of data to be processed, so I have to process data in approximately 2000 rows chunks (depending where ends the set of data with the same date). BackTest file downloads 30 days historical price data for each stock symbol, given the fact that there maybe hundreds of stocks in each sheet, it involves lots of data to be downloaded, analysed, copied to another sheet and saved in the form of separate file for each date.

It works great! Thanks a lot, appreciate your help.