CODEWITHSUNDEEP
vbaexcel
Mike Barnes
Mike Barnes

Reputation: 4305

Worksheet not being protected

I have the following code that copies one worksheet to another and pastes only values however the code that protects the sheet is not working? what am I doing wrong here?

Sub GetQuote()

    Range("AK548").Select
    Selection.Copy
    Range("AK549").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Dim ws As Worksheet
    Dim sDataOutputName As String

    With Application
        .Cursor = xlWait
        .StatusBar = "Saving Quote & Proposal Sheet..."
        .ScreenUpdating = False

         '       Copy specific sheets
         '       *SET THE SHEET NAMES TO COPY BELOW*
         '       Array("Sheet Name", "Another sheet name", "And Another"))
         '       Sheet names go inside quotes, seperated by commas
        On Error GoTo ErrCatcher
        Sheets(Array("Quote & Proposal")).Copy
        On Error GoTo 0

         '       Paste sheets as values
         '       Remove External Links, Hperlinks and hard-code formulas
         '       Make sure A1 is selected on all sheets
        For Each ws In ActiveWorkbook.Worksheets
            ws.Cells.Copy
            ws.[A1].PasteSpecial Paste:=xlValues
            ws.Cells.Hyperlinks.Delete
            Application.CutCopyMode = False
            Cells(1, 1).Select
            ws.Activate
        Next ws
        Cells(1, 1).Select

        sDataOutputName = ThisWorkbook.Path & "\" & ThisWorkbook.Worksheets("Quote Questions").Range("AK545").Value & ".xlsx"

         '       Save it with the NewName and in the same directory as original
        ActiveWorkbook.SaveCopyAs sDataOutputName
        ActiveWorkbook.Protect Password:="12345"
        ActiveWorkbook.Close SaveChanges:=False

        .Cursor = xlDefault
        .StatusBar = False
        .ScreenUpdating = True
    End With
    Exit Sub

ErrCatcher:
    MsgBox "Specified sheets do not exist within this workbook"
End Sub

Upvotes: 0

Views: 111

Answers (3)

K_B
K_B

Reputation: 3678

Your code is showing work book protection, not work sheet protection. If you want to protect the sheet, use worksheet protection:

ws.Protect Password:="12345", DrawingObjects:=True, Contents:=True, Scenarios:=True 
    'ADD AND REMOVE PARAMETERS AS YOU WANT THEM

Upvotes: 2

Mike Barnes
Mike Barnes

Reputation: 4305

I put in: ActiveSheet.Protect Password:="12345" just above the line of code: ActiveWorkbook.SaveCopyAs sDataOutputName and it worked!

Upvotes: 0

Dave Sexton
Dave Sexton

Reputation: 11188

You are protecting the workbook and setting the password, on the next line of code you closing the workbook but not saving the changes.

Upvotes: 2

Related Questions