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:
Error comes on the SaveAs line. Work with the pastevalues on each sheet, if I comment that out it errors.
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: