How to fix my bad pool header error?

Macro error message

• QUESTION: I'm not very good with Macros or VBA. I am trying to run a macro that upon opening of the workbook certain worksheets will be sorted based on information in column E starting on row 16. When I try to run the Macro I get a compile error message: Expected End With. Any suggestions on how to fix this problem or is there a better way to have the worksheets sorted upon opening? I have pasted the Macro I am using below. Thank you! Sub auto_open() ' ' Auto_Open Macro 'ActiveWorksheet.Save CutCopyMode = False With Worksheets("adam") ' ' Sort Macro ' ' Rows("16:100").Select ActiveWorkbook.Worksheets("adam").Sort.SortFields.Clear ActiveWorkbook.Worksheets("adam").Sort.SortFields.Add Key:=Range( _ "E16:E100"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("adam").Sort .SetRange Range("A16:AJ100") .Header = xlGuess .MatchCase = False .Orientation = xlTopToBot .SortMethod = xlPinYin .Apply End With With Worksheets("anders") ' ' Sort Macro ' ' Rows("16:100").Select ActiveWorkbook.Worksheets("anders").Sort.SortFields.Clear ActiveWorkbook.Worksheets("anders").Sort.SortFields.Add Key:=Range( _ "E16:E100"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("anders").Sort .SetRange Range("A16:AJ100") .Header = xlGuess .MatchCase = False .Orientation = xlTopToBot .SortMethod = xlPinYin .Apply End With End Sub ANSWER: There are two end With's missing from your code: Sub auto_open() ' ' Auto_Open Macro 'ActiveWorksheet.Save CutCopyMode = False With Worksheets("adam") ' ' Sort Macro ' ' Rows("16:100").Select ActiveWorkbook.Worksheets("adam").Sort.SortFields.Clear ActiveWorkbook.Worksheets("adam").Sort.SortFields.Add Key:=Range( _ "E16:E100"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("adam").Sort .SetRange Range("A16:AJ100") .Header = xlGuess .MatchCase = False .Orientation = xlTopToBot .SortMethod = xlPinYin .Apply End With End With With Worksheets("anders") ' ' Sort Macro ' ' Rows("16:100").Select ActiveWorkbook.Worksheets("anders").Sort.SortFields.Clear ActiveWorkbook.Worksheets("anders").Sort.SortFields.Add Key:=Range( _ "E16:E100"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("anders").Sort .SetRange Range("A16:AJ100") .Header = xlGuess .MatchCase = False .Orientation = xlTopToBot .SortMethod = xlPinYin .Apply End With End With End Sub ---------- FOLLOW-UP ---------- QUESTION: That worked great. Is there a way that I can do a series of worksheets in one sort macro rather than doing a sort macro for each individual worksheet? I probably have 30 out of 50 worksheets that I need to do the same sort.

• Answer:

  Depends on how you can determine which sheets need to be sorted and which not. Is the range to be sorted always the same? Suppose you'd want to sort all sheets: Sub SortAll() Dim oSh As Worksheet For Each oSh In Worksheets With oSh .Range("A16:AJ100").Sort Key1:=.Range("E16:E100"), Order1:=xlDescending, _ DataOption:=xlSortNormal, Header:=xlGuess, _ MatchCase:=False, Orientation:=xlTopToBot End With Next End Sub