Results 1 to 3 of 3

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

  1. #1
    Junior Member
    Join Date
    Feb 2024
    Posts
    2
    Rep Power
    0

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

    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
    Last edited by DocAElstein; 05-16-2024 at 02:51 PM.

  2. #2
    Junior Member
    Join Date
    Feb 2024
    Posts
    2
    Rep Power
    0
    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

  3. #3
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,422
    Rep Power
    10
    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

    Code:
    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
    Last edited by DocAElstein; 02-29-2024 at 10:56 PM.
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

Similar Threads

  1. Replies: 26
    Last Post: 09-26-2020, 05:56 PM
  2. Replies: 2
    Last Post: 03-08-2014, 02:49 AM
  3. Replies: 0
    Last Post: 07-07-2013, 01:52 AM
  4. Replies: 2
    Last Post: 07-02-2013, 02:36 PM
  5. Replies: 9
    Last Post: 05-31-2013, 11:31 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •