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/@alanelston2330
https://www.youtube.com/watch?v=yXaYszT11CA&lc=UgxEjo0Di9-9cnl8UnZ4AaABAg.9XYLEH1OwDIA35HNIei0z-
https://eileenslounge.com/viewtopic.php?p=316154#p316154
https://www.youtube.com/watch?v=TW3l7PkSPD4&lc=UgwAL_Jrv7yg7WWC8x14AaABAg
https://teylyn.com/2017/03/21/dollarsigns/#comment-191
https://eileenslounge.com/viewtopic.php?p=317050#p317050
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://eileenslounge.com/viewtopic.php?p=316057#p316057
https://eileenslounge.com/viewtopic.php?p=316705#p316705
https://eileenslounge.com/viewtopic.php?p=316704#p316704
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?p=316412#p316412
https://eileenslounge.com/viewtopic.php?p=316254#p316254
https://eileenslounge.com/viewtopic.php?p=316280#p316280
https://eileenslounge.com/viewtopic.php?p=315915#p315915
https://eileenslounge.com/viewtopic.php?p=315512#p315512
https://eileenslounge.com/viewtopic.php?p=315744#p315744
https://www.eileenslounge.com/viewtopic.php?p=315512#p315512
https://eileenslounge.com/viewtopic.php?p=315680#p315680
https://eileenslounge.com/viewtopic.php?p=315743#p315743
https://www.eileenslounge.com/viewtopic.php?p=315326#p315326
https://www.eileenslounge.com/viewtopic.php?f=30&t=40752
https://eileenslounge.com/viewtopic.php?p=314950#p314950
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=314920#p314920
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837
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/showt...ll=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