Hello, I am very new to Excel and trying to learn how to shorten a marco that is recorded. Looking at the VBA code below, can you please offer any tips or advice to shorten the code. I am looking more for an explination of some things as opposed to someone solving shortening the code so I can learn. Thank you!
![]()
' SortTop5_2012 Macro ' ' Keyboard Shortcut: Ctrl+t ' Filter the data to obtain the players with the highest points Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select ActiveWorkbook.Worksheets("Top30-2012").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Top30-2012").Sort.SortFields.Add Key:=Range( _ "H2:H31"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("Top30-2012").Sort .SetRange Range("B1:S31") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With ' Copy the top 5 players with the highest points from the master list to the top 5 worksheet Rows("1:6").Select Selection.Copy Sheets("Top5-2012").Select Range("A1").Select ActiveSheet.Paste Range("A1").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "Points" ' Filter the data to obtain the players with the highest goals Sheets("Top30-2012").Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select ActiveWorkbook.Worksheets("Top30-2012").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Top30-2012").Sort.SortFields.Add Key:=Range( _ "F2:F31"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("Top30-2012").Sort .SetRange Range("B1:S31") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With ' Copy the top 5 players with the highest goals from the master list to the top 5 worksheet Rows("1:6").Select Selection.Copy Sheets("Top5-2012").Select Range("A8").Select ActiveSheet.Paste Range("A8").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "Goals" ' Filter the data to obtain the players with the highest +/- Sheets("Top30-2012").Select Range("B1").Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select ActiveWorkbook.Worksheets("Top30-2012").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Top30-2012").Sort.SortFields.Add Key:=Range( _ "I2:I31"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("Top30-2012").Sort .SetRange Range("B1:S31") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With ' Copy the top 5 players with the highest +/- from the master list to the top 5 worksheet Rows("1:6").Select Selection.Copy Sheets("Top5-2012").Select Range("A15").Select ActiveSheet.Paste Range("A15").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = """+/-""" End Sub











LinkBack URL
About LinkBacks
Register To Reply

Bookmarks