I have been developing a workbook for a while now with multiple Bet Angel worksheet to do a days trading, initially on Betdaq for testing before using my main Betfair account. I realised how much Excel was being slowed down by the number of cell formulas I had added, and really did not need any to run until 15 minutes before the start time of the event.
I came up with a solution that would not only allow me to have one set of formulas that cover every worksheet, but would allow me to view the main items from any Bet Angel worksheet.
The solution simply is to use a Named Range for the Bet Angel worksheet, but this must be the same Named Range for each worksheet. The code below should be placed in a Module and a button placed on the data collection worksheet with assigned Macro SheetSelectNamedRange, by then placing number 4 say in cell "A1" and 35 in Cell "B1" the Named Range will be set to the worksheet 'Bet Angel (4)' and be 35 columns wide, the default number of columns is 31 if cell "B1"is blank, I could then Change cell "A1" to 3, press the button and the Named Range will now refer to 'Bet Angel (3)'
To get info and for cell formulas, use the cell formula Index Function such as =INDEX("apple", 1, 1) in a cell, which will always return the value from cell "A1" on whatever sheet the named range is referring to.
When I get a chance I will create a workbook and post it, unless someone else wishes to do it first.
Code: Select all
Sub SheetSelectNamedRange()
Dim wksh As Worksheet
Dim var_Worksheet_Number As Variant
Dim var_Column_Number As Variant
Set wksh = ThisWorkbook.ActiveSheet
var_Worksheet_Number = wksh.Range("A1").Value
var_Column_Number = wksh.Range("B1").Value
Call MakeNamedRange(var_Worksheet_Number, var_Column_Number)
Set wksh = Nothing
End Sub
Sub MakeNamedRange(ByVal int_Worksheet_Number As Variant, Optional ByVal int_Number_Of_Columns As Variant)
Dim ws As Worksheet
Dim wa As Worksheet
Dim Input_Error As Boolean
Dim nr As Name
Dim ra As Range
Dim bool_Name_Exists As Boolean
Dim int_Total_Number_Of_Columns As Integer
Dim str_Name_Of_Sheet As String
Input_Error = False
If VarType(int_Worksheet_Number) <> 2 Then Input_Error = True
If int_Number_Of_Columns Is Nothing Then
int_Number_Of_Columns = 31
Else
If VarType(int_Number_Of_Columns) <> 2 Then Input_Error = True
End If
If Input_Error = False Then
If int_Worksheet_Number < 1 Or int_Worksheet_Number > 10 Then Input_Error = True
If int_Number_Of_Columns < 31 Or int_Number_Of_Columns > 500 Then Input_Error = True
End If
If Input_Error = False Then
Application.ScreenUpdating = False
Set ws = ThisWorkbook.ActiveSheet
If int_Worksheet_Number = 1 Then
str_Name_Of_Sheet = "Bet Angel"
Else
str_Name_Of_Sheet = "Bet Angel (" & int_Worksheet_Number & ")"
End If
Set wa = ThisWorkbook.Sheets(str_Name_Of_Sheet)
wa.Select
Set ra = Range(Cells(1, 1), Cells(100, int_Number_Of_Columns))
bool_Name_Exists = False
For Each nr In ThisWorkbook.Names
If "apple" = nr.Name Then bool_Name_Exists = True
Next nr
If bool_Name_Exists = False Then
ThisWorkbook.Names.Add Name:="apple", RefersTo:=ra
Else
ThisWorkbook.Names("apple").RefersTo = ra
End If
ws.Select
Set wa = Nothing
Set ws = Nothing
Application.ScreenUpdating = True
End If
End Sub