VBA split files - confirmed saved in directory, but directory is blank

[merkyfitz]

Hi All, I wonder if you can help me!

I have a macro which has worked fine to split out a single sheet into multiple files based on change in Manager name in selected column. It does a number of things, protects, password protects and files as the cell content in to a 'split' directory.

I only use it a couple of times a year, last time in October. In October it was a very slow process because for each file being saved I had to confirm the category of file - whether confidential, internal etc.

I have gone to test it today, and the macro seems to be going through the motions when I run it, you see the screen flicking as if saving etc, and the confirms at the end the 6 files have been saved to 'split' directory in same place as original file. However, when I go there the directory is empty - even when looking in directory properties there is no file count.

I am totally confused by this!! Any advice you can give me would be very gratefully received!

Many thanks!


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/@alanelston2330 (https://www.youtube.com/@alanelston2330)
https://www.youtube.com/watch?v=yXaYszT11CA&lc=UgxEjo0Di9-9cnl8UnZ4AaABAg.9XYLEH1OwDIA35HNIei0z- (https://www.youtube.com/watch?v=yXaYszT11CA&lc=UgxEjo0Di9-9cnl8UnZ4AaABAg.9XYLEH1OwDIA35HNIei0z-)
https://eileenslounge.com/viewtopic.php?p=316154#p316154 (https://eileenslounge.com/viewtopic.php?p=316154#p316154)
https://www.youtube.com/watch?v=TW3l7PkSPD4&lc=UgwAL_Jrv7yg7WWC8x14AaABAg (https://www.youtube.com/watch?v=TW3l7PkSPD4&lc=UgwAL_Jrv7yg7WWC8x14AaABAg)
https://teylyn.com/2017/03/21/dollarsigns/#comment-191 (https://teylyn.com/2017/03/21/dollarsigns/#comment-191)
https://eileenslounge.com/viewtopic.php?p=317050#p317050 (https://eileenslounge.com/viewtopic.php?p=317050#p317050)
https://eileenslounge.com/viewtopic.php?f=27&t=40953&p=316854#p316854 (https://eileenslounge.com/viewtopic.php?f=27&t=40953&p=316854#p316854)
https://www.eileenslounge.com/viewtopic.php?v=27&t=40953&p=316875#p316875 (https://www.eileenslounge.com/viewtopic.php?v=27&t=40953&p=316875#p316875)
https://eileenslounge.com/viewtopic.php?p=316057#p316057 (https://eileenslounge.com/viewtopic.php?p=316057#p316057)
https://eileenslounge.com/viewtopic.php?p=316705#p316705 (https://eileenslounge.com/viewtopic.php?p=316705#p316705)
https://eileenslounge.com/viewtopic.php?p=316704#p316704 (https://eileenslounge.com/viewtopic.php?p=316704#p316704)
https://eileenslounge.com/viewtopic.php?p=176255#p176255 (https://eileenslounge.com/viewtopic.php?p=176255#p176255)
https://eileenslounge.com/viewtopic.php?f=27&t=40919&p=316597#p316597 (https://eileenslounge.com/viewtopic.php?f=27&t=40919&p=316597#p316597)
https://eileenslounge.com/viewtopic.php?p=316412#p316412 (https://eileenslounge.com/viewtopic.php?p=316412#p316412)
https://eileenslounge.com/viewtopic.php?p=316254#p316254 (https://eileenslounge.com/viewtopic.php?p=316254#p316254)
https://eileenslounge.com/viewtopic.php?p=316280#p316280 (https://eileenslounge.com/viewtopic.php?p=316280#p316280)
https://eileenslounge.com/viewtopic.php?p=315915#p315915 (https://eileenslounge.com/viewtopic.php?p=315915#p315915)
https://eileenslounge.com/viewtopic.php?p=315512#p315512 (https://eileenslounge.com/viewtopic.php?p=315512#p315512)
https://eileenslounge.com/viewtopic.php?p=315744#p315744 (https://eileenslounge.com/viewtopic.php?p=315744#p315744)
https://www.eileenslounge.com/viewtopic.php?p=315512#p315512 (https://www.eileenslounge.com/viewtopic.php?p=315512#p315512)
https://eileenslounge.com/viewtopic.php?p=315680#p315680 (https://eileenslounge.com/viewtopic.php?p=315680#p315680)
https://eileenslounge.com/viewtopic.php?p=315743#p315743 (https://eileenslounge.com/viewtopic.php?p=315743#p315743)
https://www.eileenslounge.com/viewtopic.php?p=315326#p315326 (https://www.eileenslounge.com/viewtopic.php?p=315326#p315326)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40752 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40752)
https://eileenslounge.com/viewtopic.php?p=314950#p314950 (https://eileenslounge.com/viewtopic.php?p=314950#p314950)
https://www.eileenslounge.com/viewtopic.php?p=314940#p314940 (https://www.eileenslounge.com/viewtopic.php?p=314940#p314940)
https://www.eileenslounge.com/viewtopic.php?p=314926#p314926 (https://www.eileenslounge.com/viewtopic.php?p=314926#p314926)
https://www.eileenslounge.com/viewtopic.php?p=314920#p314920 (https://www.eileenslounge.com/viewtopic.php?p=314920#p314920)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[merkyfitz]

I saw a similar post on another forum that led me to solve this! There were two lines in the script that said the following and were preventing the file categorisation option to pop up. I deleted the two below and seem to be on track!

Application.ScreenUpdating = False
Application.DisplayAlerts = False

If you know of any problems which may arise by deleting these please do let me know! Thank you :)

[DocAElstein]

Hello merkyfitz
Welcome to ExcelFox
Thanks for the update, good to see you making progress.
Application.ScreenUpdating = False is something that can help speed up a macro. It does something along the lines of what it suggests, - it does not continually update the screen, as would normally be the case, and as you would normally want. You should see in such a code a Application.ScreenUpdating = True at some further point. Personally I would be vary of using it. Sometimes something my go wrong, and Excel gets left in this Application.ScreenUpdating = False state and things either do not get done, or you can’t see them being done
Application.DisplayAlerts = False is similar. It prevents automatic warning pop ups coming up. For example, if some annoying pop up always asked you to confirm something, which you always did and always would, then this would get rid of that nuisance.
But once again, you should and would usually have correspondingly a Application.DisplayAlerts = True later in the coding and usually quite close to the Application.DisplayAlerts = False



If you suspect that something may have inadvertently left these sort of things in the False state, ( which is almost always a bad state to be in permanently), then this short coding will usually bring things in order. (If things are already in order, then running this coding won’t do anything or cause any problems. So it rarely does any harm to try it, and it might get you out of a jam


Sub Oops() ' https://www.excelfox.com/forum/showthread.php/2954-VBA-split-files-confirmed-saved-in-directory-but-directory-is-blank?p=24020&viewfull=1#post24020
Let Application.ScreenUpdating = True
Let Application.DisplayAlerts = True
Let Application.Calculation = xlCalculationAutomatic
End Sub


Usually if in doubt, or when trying to debug or modify any coding I would always first remove or 'comment out any of the lines of that type, in particular the ones making things False , then run the Oops macro.

Once you are finished, and all is well, then you can consider using some of those code lines which should always have a corresponding .True somewhere later after the .False


Alan