VBA to split worksheets going through motions and confirming saved in directory... but nothing there when I check!

[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 identified directory. 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!

 

[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

 

[Joe4]

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

Those two lines are not necessary for your code to run.
Those two things are usually just adding to suppress screen updating and pop-ups while code is running. They are often used to help "speed up the code".
If you have things like loops and are updating a lot of data on the sheet, the time saving may not be trivial.

Usually, you can put them in strategic places in your code to give you the desired benefit without the issues you experienced.
If your code runs pretty fast without those lines in there, then I think you can just remove them altogether.
If your code takes a while to run, you may want to include, but just put them in a more strategic spot in your code.
We could probably help with that, if you posted your entire VBA procedure code.