VBA: .SaveAs a protected workbook

pplstuff

Well-known Member
Joined
Mar 9, 2012
Messages
951
Excel 2007.

I'm trying to create a number of files from a "master" workbook. These files will all be locked/password protected so users cannot make changes. For some some reason, I can't saveas a workbook unless I've pastespecial xlvalues each sheet. Can anyone see where I'm going wrong?

The error msg is...
Run-time '1004'
"The cell or chart that you are trying to changes is protected and therefore read only..."

Relevant code:
Code:
'Copy targeted sheets to new workbook
            Sheets(Array("Instructions", "Summary Sales Plan", "Personnel Plan", "MT&E Plan", "P&L Plan", "Summary")).Copy
            
            'Set variable
            Set wbTemp = ActiveWorkbook
            
            'For each worksheet in temp workbook
            For Each ws In wbTemp.Sheets
                With ws
                    'Paste values
                    .Activate
                    .Cells.Copy
                    .Range("A1").Select
                    Selection.PasteSpecial Paste:=xlValues
                    'Protect sheet
                    .EnableSelection = xlNoRestrictions
                    .Protect "Planning", True, True, True, False, False, False, False, False, False, False, False, False, False, False, False
                End With
            Next ws
            
            'Protect workbook
            wbTemp.Protect Password:="Planning", structure:=True, Windows:=False
            
            'Save workbook
            wbTemp.SaveAs ThisWorkbook.Sheets("Control Panel").Range("C7") & "Testing - 2014 Branch Planning " & rBranch.Offset(0, 2) & ".xlsx", 51
            
            'Close workbook
            wbTemp.Close False

Error comes on the SaveAs line. Work with the pastevalues on each sheet, if I comment that out it errors.
 
Last edited:

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Solved.

I was breaking links to various .dlls after protecting the workbook.

Breaking links before the .protect solved the issue. Updated the code in the OP for reference.
 
Upvote 0

Forum statistics

Threads
1,214,830
Messages
6,121,834
Members
449,051
Latest member
excelquestion515

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top