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]

I think you can change:
ActiveWorkbook.Worksheets("RBa").Sort.SortFields.Clear

To:
ActiveWorkbook.Activesheet.Sort.SortFields.Clear

Change Worksheets("RBa") to Activesheet every place in the script.

 

[mumps]

I've tidied up your code a bit. The macro will loop through all the sheets and perform the action on each sheet. If you need to restrict the action to only certain sheets, I would need to know the names of those sheets.

Sub UpdateIndex()
    ' UpdateIndex Macro
    ' Updates the HDCP Index based on the last top 10 scores recorded.
    ' Keyboard Shortcut: Ctrl+Shift+U
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    For Each ws In Sheets
        ws.Range("A14:A34,I14:I34").ClearContents
        ws.Range("B14:H33").Select
        ws.Sort.SortFields.Clear
        ws.Sort.SortFields.Add Key:=ws.Range("H14:H33"), _
           SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With ws.Sort
           .SetRange ws.Range("B14:H33")
           .Header = xlGuess
           .MatchCase = False
           .Orientation = xlTopToBottom
           .SortMethod = xlPinYin
           .Apply
        End With
        ws.Range("I14:I23") = "*"
        ws.Range("B14:J33").Select
        ws.Sort.SortFields.Clear
        ws.Sort.SortFields.Add Key:=ws.Range("B14:B33"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        With ws.Sort
           .SetRange ws.Range("B14:J33")
           .Header = xlGuess
           .MatchCase = False
           .Orientation = xlTopToBottom
           .SortMethod = xlPinYin
           .Apply
        End With
        ws.Range("H10").FormulaR1C1 = "=TRUNC(0.96*(SUM(R14C10:R33C10)/10),1)"
    Application.ScreenUpdating = True
 End Sub

 

[My Aswer Is This]

Do you want to run this same script on 10 different sheets at the same time?
I could write a script to do all 10 sheets.

If so tell me what sheets.
Tell me for example sheets 1 to 10
Or sheets 2 to 12

Sheets are numbered according to how they are lined up on the tab bar
Sheet 1 is on the far left and sheet 2 is next one to the right etc.

 

[DonEB]

I think you can change:
ActiveWorkbook.Worksheets("RBa").Sort.SortFields.Clear

To:
ActiveWorkbook.Activesheet.Sort.SortFields.Clear

Change Worksheets("RBa") to Activesheet every place in the script.

Thank you very much. This seems to be working for me. One thing that I forgot to mention but may have an impact on the answer is... this spreadsheet may be circulated and utilized by different people (i.e., whomever may be assigned to be responsible for maintaining handicaps). Everyone may not have version 10 of Excel but no version should be older than 97 - 2003. Will this Macro work in the older versions as well?

Again, thanks for you help.

 

[DonEB]

Thank you for your efforts. I'm currently looking to restrict the action to individual sheets. While I can supply the names of those sheets, those sheet names can change in the future as new players are added and/or removed. So, having a generic script would be best. However, I will be holding onto this code because it provides me with a good option of conducting and a single update to all the records. My final question is, will this code work in versions of Excel that go back to 97 - 2003?

Again, thank you very much for you time and effort.

 

[My Aswer Is This]

Not sure if all this code will work on all versions of Excel.
I would think it would I see no newer type coding.
As far as sheet names changing if we just used sheet numbers the name of the sheet is not considered.
Check back with me if you need more help.

 

[DonEB]

Not sure if all this code will work on all versions of Excel.
I would think it would I see no newer type coding.
As far as sheet names changing if we just used sheet numbers the name of the sheet is not considered.
Check back with me if you need more help.

Just finished meeting with one person who is using Excel 2003 and, after jumping through a few hoops to get the program to open up in Excel 2003, I discovered that I have two issues with the Macro you assisted me with. To get the program to open up, I had to go into Excel and modify his security level for macros down to Medium. Then, upon opening up the file, it would ask me if I wanted to enable the macros. I would hit the enable button.

The first issue involved the macro stopping and getting stuck at the following code (i.e., went into debugging mode):
ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear

There are four additional lines of code that is similar to the above. I couldn't get past the first roadblock but I'm assuming the others will cause an issue as well. The question is... is there an alternative language that can be used to get me past those points?

The second issue was that the Keyboard Shortcuts didn't work. This is not as critical but thought you might want to know in case you had an opinion on how to get them to work.

 

[My Aswer Is This]

Try changing the code back to this:
ActiveWorkbook.Worksheets("RBa").Sort.SortFields.Clear

And name your sheet "RBa" and see if this works.

To assign a shortcut key to your Macro you need to do this:
1. Choose View from the ribbon
2. Choose Macros
3. Choose View Macros
4. Choose UndateIndex this is the name of your Macro
5. Choose Options
6. In the little tix box enter the shortcut key you want

 

[DonEB]

Try changing the code back to this:
ActiveWorkbook.Worksheets("RBa").Sort.SortFields.Clear

And name your sheet "RBa" and see if this works.

To assign a shortcut key to your Macro you need to do this:
1. Choose View from the ribbon
2. Choose Macros
3. Choose View Macros
4. Choose UndateIndex this is the name of your Macro
5. Choose Options
6. In the little tix box enter the shortcut key you want

I can attempt this suggestion tomorrow (Saturday) since Excel 2003 is on my neighbors computer. However, we made the original conversion to "ActiveSheet" to allow the Macro to be utilized across multiple sheets/pages. Changing it back to ".Worksheets("RBa")" would bring me back to the original problem... correct? Or is your intent to see if the original code would work for a the single sheet/page and then work from there?

Also, I will try to assign a shortcut key to the Macro within Excel 2003 tomorrow as well. I assumed that the entire Macro (including the shortcut key) would transport with the spreadsheet. So, I never gave any thought to the possibility that the shortcut key may have been lost during the process of moving from one computer to the next.

Thanks again for your help.