Macro Error

[ghrek]

Hi I have the following macro that comes with compile error expected end with.

Looking online it says I should have END SUB but already got that. Any ideas?

Sub printshiftsheet()
'
' printshiftsheet Macro
'

    Const cPassword As String = "ccc"
   
    Dim oWs As Worksheet
    Set oWs = ThisWorkbook.Worksheets("ShiftSheet")

    With oWs
        .Unprotect Password:=cPassword
         Range("B10:D23").Select
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
 
    ActiveWorkbook.Save
        .PageSetup.PrintArea = "$A$3:$H$53"
        Range("B10:D23").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .PatternTintAndShade = 0
         .Range("D8").Value = .Range("D8").Value + 1
        Application.Dialogs(xlDialogPrint).Show
        .Cells.Locked = True
        .Cells.FormulaHidden = False
        .Range("C5:D7,H11:H22,H36:H40,D34:D38,B46:H47,G56:H56,C60:C71,G77:H77,C81:C88,G93:H93,C94:C101").Locked = False
        .Protect Password:=cPassword, DrawingObjects:=False, Contents:=True, Scenarios:=False
        oWs.Parent.Save

End Sub

 

[Fluff]

expected end with.

Is exactly correct, you have 3 With statements, but no End With.

 

[ghrek]

so I do

End With
End Sub ??

 

[Fluff]

You will need an End With after each of your With sections.

 

[ghrek]

Ive tried doing the end with in all manners and keep coming up with an error.

What have I done wrong. Macro below

    Const cPassword As String = "shreked"
    
    Dim oWs As Worksheet
    Set oWs = ThisWorkbook.Worksheets("ShiftSheet")

    With oWs
        .Unprotect Password:=cPassword
         .Range("B10:D23").Select
         Selection.Interior
        Pattern = xlSolid
        PatternColorIndex = xlAutomatic
        ThemeColor = xlThemeColorLight1
        TintAndShade = 0
        PatternTintAndShade = 0
        Active.Workbook.Save
        .PageSetup.PrintArea = "$B$3:$H$54"
        Application.Dialogs(xlDialogPrint).Show
        End With
         With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 4.99893185216834E-02
        .PatternTintAndShade = 0
        Active.Workbook.Save
        Cells.Locked = True
        Cells.FormulaHidden = False
        .Range("C5:D7,H11:H22,D34:D38,B46:H47,G56:H56,C60:C71,G77:H77,C81:C88,G93:H93,C94:C101").Locked = False
        .Protect Password:=cPassword, DrawingObjects:=False, Contents:=True, Scenarios:=False
        oWs.Parent.Save
End With
End Sub

 

[Fluff]

Maybe

   With oWs
      .Unprotect Password:=cPassword
      With .Range("B10:D23").Interior
         .Pattern = xlNone
         .TintAndShade = 0
         .PatternTintAndShade = 0
      End With
      ActiveWorkbook.Save
      .PageSetup.PrintArea = "$A$3:$H$53"
      With .Range("B10:D23").Interior
         .Pattern = xlSolid
         .PatternColorIndex = xlAutomatic
         .ThemeColor = xlThemeColorLight1
         .TintAndShade = 0
         .PatternTintAndShade = 0
      End With
      .Range("D8").Value = .Range("D8").Value + 1
      Application.Dialogs(xlDialogPrint).Show
      .Cells.Locked = True
      .Cells.FormulaHidden = False
      .Range("C5:D7,H11:H22,H36:H40,D34:D38,B46:H47,G56:H56,C60:C71,G77:H77,C81:C88,G93:H93,C94:C101").Locked = False
      .Protect Password:=cPassword, DrawingObjects:=False, Contents:=True, Scenarios:=False
   End With
   oWs.Parent.Save

 

[ghrek]

thanks fluff

 

[Fluff]

You're welcome & thanks for the feedback.