Split Workbook VBA w/Default Value for Sensitivity Labels

[emacleod]

I had needed assistance in saving out individual worksheets as separate files from one workbook. The VBA I'm providing below has taken care of this need. However, the organization I work for as recently implemented Sensitivity Labels throughout the Office 365 apps. So, when I run this VBA, I am prompted with the Excel dialog box that requires me to select a value as each worksheet is being created as a workbook. As you can imagine, this slows down the process of running the VBA. I then have to click to select "Confidential" for each time it comes up. I have been unable to locate and add-in additional logic to the existing VBA which would automatically select "Confidential" when the Sensitivity Label is being prompted for entry. So, my ask is the following, please - "Can this existing VBA be revised to account for entering the value of "Confidential" when prompted for the Security Level?" As always, appreciate the assistance! – Thank You!

Here's a link from Microsoft concerning the implementation of Sensitivity Labels - Apply sensitivity labels to your files and email - Microsoft Support

Here is the existing VBA I need to have revised -

Sub SplitEachWorksheet()
Dim FPath As String
FPath = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each ws In ThisWorkbook.Sheets
ws.Copy
Application.ActiveWorkbook.SaveAs Filename:=FPath & "\" & ws.Name & ".xlsx"
Application.ActiveWorkbook.Close False
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

 

[Jeffrey Mahoney]

Change Sensitivity Label Programatically

Jeffrey Mahoney submitted a new Excel article: Change Sensitivity Label Programatically - Change Sensitivity Label Azure Company Policy VBA Macro This code will allow you to change a workbook's Sensitivity Label using VBA. It seems that each company gets to decide what those labels are. I...

www.mrexcel.com