Password to save worksheet as new workbook

[Aussiebear]

Having a seniors moment here.... I am currently trying save each worksheet as a new workbook with the following code


Sub CopyWorkbooks()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
ws.Copy
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & ws.Name
Next ws
Application.ScreenUpdating = True
End Sub


However I'm blocked from saving because it requires a password. How is this done?

[Paul_Hossler]

I couldn't reproduce the issue.

Attached WB has 4 sheets, Sheet1 is PW protected, Sheet4 is Hidden (a WB has to have at least 1 visible WS and copying a hidden sheet as a new workbook doesn't work)

I did add a bit to your macro



Option Explicit


Sub CopyWorkbooks()
Dim ws As Worksheet


Application.ScreenUpdating = False


For Each ws In ThisWorkbook.Worksheets

If ws.Visible = xlSheetVisible Then

On Error Resume Next
Application.DisplayAlerts = False
Kill ThisWorkbook.Path & "\" & ws.Name
Application.DisplayAlerts = False
On Error GoTo 0

ws.Copy

ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & ws.Name
ActiveWorkbook.Close False
ThisWorkbook.Activate
End If
Next ws


Application.ScreenUpdating = True
End Sub

[Aussiebear]

I can manually create and save a new workbook, but all of a sudden yesterday I now need to supply my system user password. Could this be because of wanting save multiple files at once and is a security protocol on a Mac system?