Splitting worksheets in Excel with Senstivity Labels following master file using VBA

[liclice]

I am trying to split the worksheets in my excel files into different files saving under the worksheet names using the following code:


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

However, I cannot get the files saved as sensitivity label is required. Are there any ways to amend the above code in order to save the files based on the master file's label?

Thanks a lot.

 

[NdNoviceHlp]

Hi *liclice and Welcome to the Board! Here's a variation of @jolivanes code posted here...

Copy multiple sheets to a new workbook

Welcome. I have a workbook consisting of several sheets. I want to copy all sheets to a new workbook ignoring only one sheet Sheet1, and save the new workbook in the same default path in xlsx format.

www.mrexcel.com

Sub SaveSheet()
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
Sheets(sh.Name).Copy
    With ActiveWorkbook
            Application.DisplayAlerts = False
                .SaveAs ThisWorkbook.Path & "\" & sh.Name & ".xlsx", FileFormat:=51
            Application.DisplayAlerts = True
        .Close
    End With
Next sh
End Sub

HTH. Dave
ps. Please use code tags

 

[liclice]

Hi *liclice and Welcome to the Board! Here's a variation of @jolivanes code posted here...

Copy multiple sheets to a new workbook

Welcome. I have a workbook consisting of several sheets. I want to copy all sheets to a new workbook ignoring only one sheet Sheet1, and save the new workbook in the same default path in xlsx format.

www.mrexcel.com

Sub SaveSheet()
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
Sheets(sh.Name).Copy
    With ActiveWorkbook
            Application.DisplayAlerts = False
                .SaveAs ThisWorkbook.Path & "\" & sh.Name & ".xlsx", FileFormat:=51
            Application.DisplayAlerts = True
        .Close
    End With
Next sh
End Sub

HTH. Dave
ps. Please use code tags

Thank you Dave but unfortunately I tried all of the codes they all don't work and give error messages.

 

[NdNoviceHlp]

I trialed the code above, and re-trialed again before posting, and it works ok. What is the error message? Is this a onedrive file or stored on your pc? It probably won't work for a onedrive file. Dave

 

[liclice]

I trialed the code above, and re-trialed again before posting, and it works ok. What is the error message? Is this a onedrive file or stored on your pc? It probably won't work for a onedrive file. Dave

Hello Dave, I was trying to store in a network drive.

 

[NdNoviceHlp]

For a network drive, you will need to first save it to your pc (use SaveCopyAs), generate your new workbooks (with code above) and then use CopyFile to place the new wbs on the network. You can then delete the wbs on your pc. Perhaps there's an easier more direct route that others will share? Dave