Compile Error: Expected End With

[aggiemarine07]

Howdy! I am definitetly a novice at VBA and I am hoping someone with more VBA skills than myself can spot the error below. I keep getting a "Compile Error: Expected End With" error. This code is supposed to auto sort data in a few columns as information gets added. Here is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
Range("P5:Y156").Select
ActiveWorkbook.Worksheets("COMBINED").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("COMBINED").Sort.SortFields.Add Key:=Range( _
        "P5:P156"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("COMBINED").Sort.SortFields.Add Key:=Range( _
        "R5:R156"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("COMBINED").Sort
        .SetRange Range("P4:Y156")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
End Sub

Any help would be greatly appreciated. Thanks!

 

[VoG]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
Range("P5:Y156").Select
ActiveWorkbook.Worksheets("COMBINED").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("COMBINED").Sort.SortFields.Add Key:=Range( _
"P5:P156"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("COMBINED").Sort.SortFields.Add Key:=Range( _
"R5:R156"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("COMBINED").Sort
    .SetRange Range("P4:Y156")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
End Sub

 

[par60056]

Whenever you have a "With" statement like "With ActiveWorkbook.Worksheets("COMBINED").Sort" you need to pair it with an "End With" The "End With" should be right after the ".Apply"

The With statement is a shortcut and you need to tell the compiler when you are done using that shortcut.

 

[aggiemarine07]

Thanks, that did it! I thought it was something incredibly simple.