Excel 10 macro to be used by many pages (one at at time) to perform calculations

[DonEB]

I recently inherited an Excel Spreadsheet which is designed to establish a Handicap Index based identifing the best 10 scores from the last 20 scores recorded. There are appoximately 24 different pages that needed to be updated (one at at time) after their records have been updated.

I was able to successfully record a macro that allowed me to sort data, identify lowest 10 scores, and then calculate handicap index for a specific page. Here is that code:
Sub UpdateIndex()
'
' UpdateIndex Macro
' Updates the HDCP Index based on the last top 10 scores recorded.
'
' Keyboard Shortcut: Ctrl+Shift+U
'
Range("A14:A34").Select
Selection.ClearContents
Range("I14:I34").Select
Selection.ClearContents
Range("B14:H33").Select
ActiveWorkbook.Worksheets("RBa").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("RBa").Sort.SortFields.Add Key:=Range("H14:H33"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("RBa").Sort
.SetRange Range("B14:H33")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("I14").Select
ActiveCell.FormulaR1C1 = "*"
Range("I15").Select
ActiveCell.FormulaR1C1 = "*"
Range("I16").Select
ActiveCell.FormulaR1C1 = "*"
Range("I17").Select
ActiveCell.FormulaR1C1 = "*"
Range("I18").Select
ActiveCell.FormulaR1C1 = "*"
Range("I19").Select
ActiveCell.FormulaR1C1 = "*"
Range("I20").Select
ActiveCell.FormulaR1C1 = "*"
Range("I21").Select
ActiveCell.FormulaR1C1 = "*"
Range("I22").Select
ActiveCell.FormulaR1C1 = "*"
Range("I23").Select
ActiveCell.FormulaR1C1 = "*"
Range("B14:J33").Select
ActiveWorkbook.Worksheets("RBa").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("RBa").Sort.SortFields.Add Key:=Range("B14:B33"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("RBa").Sort
.SetRange Range("B14:J33")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("H10").Select
ActiveCell.FormulaR1C1 = "=TRUNC(0.96*(SUM(R14C10:R33C10)/10),1)"
Range("J6").Select
End Sub

I realize that this macro makes specific reference to the page on which it was created (i.e., ActiveWorkbook.Worksheets("RBa")). Based on this, the Macro only seems to work when in that page. My problem is that i do not know how to replace that code which restricts where this macro will work with code that would make it more generic.

Any help would be greatly appreciated.

Thank!

 

[My Aswer Is This]

My thought on changing it back was just to see if that was the problem. If it was then we would have to sort out why. Where the shortcut key is stored is a mystery to me. It was a discussion here last week and I never understood what the answer was. Maybe the function SortFields.clear will not work with Excel 2003. Try taking out any line of code with this statement and see how things work.