Compile Error: Expected End With

[karldugan]

Hello all,

Been building some basic macro's and started on a more complex one (below) when I left it last, it would run fine and do what I want. Since I've come back to it though it has stopped working and comes up with the Compile Error: Expected End With and End Sub is highlighted.

Could someone explain (in simple as possible way) what the error actually means and also point to where it is going wrong/how to fix?

Thanks in advance for any help provided.

Public Sub Michelle()
'
' Macro for Michelle
ActiveWorkbook.Worksheets("March Other Sources").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("March Other Sources").Sort.SortFields.Add Key:= _
Range("U5:U5000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
:=xlSortNormal
ActiveWorkbook.Worksheets("March Other Sources").Sort.SortFields.Add Key:= _
Range("G5:G5000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
:=xlSortNormal
With ActiveWorkbook.Worksheets("March Other Sources").Sort
.SetRange Range("A4:AF5000")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
Sheets("March Other Sources").Select
Columns("U:U").Select
Selection.Copy
Sheets("Unique Values").Select
Columns("A:A").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveSheet.Range("$A$1:$A$967").RemoveDuplicates Columns:=1, Header:=xlNo
Columns("A").Replace What:="/", _
Replacement:="&", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False

Dim MyCell As Range, MyRange As Range

Set MyRange = Sheets("Unique Values").Range("A3")
Set MyRange = Range(MyRange, MyRange.End(xlDown))
For Each MyCell In MyRange
Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
Next MyCell
End Sub

 

[Joe4]

Welcome to the Board!

The error code is actually telling you exactly what your issue is. Every time you use a "With" statement, you need a corresponding "End With" statement to mark the end of the With statement. You have a "With", but no "End With". It looks like it should be right after your ".Apply" line.

 

[steve the fish]

Im afraid that could never have run unless you deleted End With without noticing. Any its here its needed:

 With ActiveWorkbook.Worksheets("March Other Sources").Sort
 .SetRange Range("A4:AF5000")
 .Header = xlYes
 .MatchCase = False
 .Orientation = xlTopToBottom
 .SortMethod = xlPinYin
 .Apply
End With

 

[karldugan]

Welcome to the Board!

The error code is actually telling you exactly what your issue is. Every time you use a "With" statement, you need a corresponding "End With" statement to mark the end of the With statement. You have a "With", but no "End With". It looks like it should be right after your ".Apply" line.

Thanks for the quick response! Knew I'd get the answer from here

 

[Joe4]

You are welcome!

Be sure to pay special attention to the error messages that are returned. Many times, they are cryptic, and it is tough to figure out what it is trying to tell you, but other times it is a little clearer and pretty indicative of what the problem actually is.