Welcome to the FTS Forum - Read the introduction here

Avatar
Please consider registering
Guest
Search
Forum Scope


Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Feed
Topic Rating: +11 Topic Rating: +11 (11 votes) 
sp_TopicIcon
Is there a way to run macros at the same time for the databases?
Avatar
kingnpuk
Beginner
Members
Forum Posts: 8
Member Since:
August 20, 2013
sp_UserOfflineSmall Offline
1
April 10, 2020 - 11:56 am
sp_Permalink sp_Print
0

For example on the Ultimate spreadsheet how can I run all macros at the press of a button so it either opens up a excel sheet for each macro or one sheet with the results all on one sheet?

is there a way? thanks

Avatar
ArtTest
Moderator
Moderators

Members
Forum Posts: 259
Member Since:
March 6, 2016
sp_UserOfflineSmall Offline
2
April 10, 2020 - 12:28 pm
sp_Permalink sp_Print
+1

Yes, but it requires some VBA knowledge. It is perfectly possible to get 1 macro to run others and to collate and display results, but this is not a task for a VBA beginner.

Avatar
mrwhitney
Beginner
Members
Forum Posts: 43
Member Since:
September 12, 2019
sp_UserOfflineSmall Offline
3
May 3, 2020 - 3:28 pm
sp_Permalink sp_Print
0

Hi all, is there a way to highlight future fixtures found by Ultimate macros?

I’d like to rewrite my macros so that I can automatically copy upcoming fixtures to a separate spreadsheet I’ve been using, which has each system / macro on a separate tab. If I could highlight fixtures from todays date and all future dates then I can use Windows automation (a free Windows macro programme called TinyTask) to copy the info to my results spreadsheet automatically, saving time and reducing the risk of mistakes.

The only bit I can’t work out is how to highlight new fixtures… thanks

Avatar
ArtTest
Moderator
Moderators

Members
Forum Posts: 259
Member Since:
March 6, 2016
sp_UserOfflineSmall Offline
4
May 3, 2020 - 3:35 pm
sp_Permalink sp_Print
+1

if you put you cursor on the first correct date, you can cycle through

 

int row = activecell.row

while activecell <> “”

    activecell.range(“A” & row & “:Z” & row).Interior.Color = RGB(255, 0, 0)

    activecell.offset(1,0).activate

   row = activecell.row

wend

 

or something like that, may need a little debugging. You can google RGB codes.

Avatar
mrwhitney
Beginner
Members
Forum Posts: 43
Member Since:
September 12, 2019
sp_UserOfflineSmall Offline
5
May 3, 2020 - 3:46 pm
sp_Permalink sp_Print
0

Lovely, thanks Art, that points me in the right direction.

Avatar
Matthew
Beginner
Members
Forum Posts: 13
Member Since:
February 23, 2019
sp_UserOfflineSmall Offline
6
May 4, 2020 - 7:47 pm
sp_Permalink sp_Print
+2

I have written some VBA that does exactly this from within excel

When you have run your macro it looks for all the games without a result copies them and pastes them into a new spreadsheet. Happy to share if your interested

The following users say thank you to Matthew for this useful post:

jonnyhammer, mrwhitney, bettyblogger
Avatar
jonnyhammer
Beginner
Members
Forum Posts: 7
Member Since:
July 12, 2018
sp_UserOfflineSmall Offline
7
May 5, 2020 - 1:26 pm
sp_Permalink sp_Print
0

Would love to have VBA gizmo Matthew, a potentially massive timesaver as i run so many micro systems rather than one or two main ones. It currently takes me 30 mins+ to run the macros each time plus the time to then enter them into a spreadsheet.

Really appreciate you sharing if you are OK with it, VBA is totally beyond old gits like me, so might need some guidance how to apply/install it if you dont mind

Cheers

Jonny

Avatar
Matthew
Beginner
Members
Forum Posts: 13
Member Since:
February 23, 2019
sp_UserOfflineSmall Offline
8
May 5, 2020 - 5:50 pm
sp_Permalink sp_Print
+3

Yes no problem it maybe better if I describe my process as I run 2 macros to automate it

First I download the weekly sheet into a folder I then open my master spreadsheet (which contains my strategy macros) I click a button which executes the following code to copy the data into the master spreadsheet from the weekly sheet.

Dim WeeklySheet As Workbook
Dim CurrentWorkbook As Workbook

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set CurrentWorkbook = ThisWorkbook

Set WeeklySheet = Workbooks.Open(“C:\Users\mjdav\Documents\FTS\Weekly odds sheets\WeeklyOdds.xlsm”, True, True) ‘ opens the weekly sheet

Range(“A4”, Range(“A3”).End(xlDown).Range(“BH4”)).Copy ‘copies all the data from weekly sheet

ThisWorkbook.Activate
Range(“A10”).End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteAllUsingSourceTheme ‘pastes weekly sheet data in my master spreadsheet

WeeklySheet.Close
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

I then run my startegy Macros and after each instance I then click a button which runs the code below. This copies the games selected by the strategy macro into another spreadsheet called results

Sub Copy_Current_Games()

Dim Results As Workbook
Dim MySheet As String

Application.ScreenUpdating = False ‘makes sure the spreadsheet I am copying to doesn’t open. Everything is done in the background

MySheet = Worksheets(“FTS Master Odds Sheet”).Cells(7, 1).Value ‘uses this to ref the sheet name in results sheet

Range(“P10”).End(xlDown).Offset(1, 0).Select ‘finds the first empty cell in the FT column after P10

Range(ActiveCell, ActiveCell.Offset(0, -15).End(xlDown)).Copy ‘Copies the cells from the first empty cell after P10 to the A column 15 cells to the left and to the end

Set Results = Workbooks.Open(“H:\Football Trading 18-19\FTS\Results.xlsm”) ‘Opens the results spreadsheet

Results.Worksheets(MySheet).Activate ‘Activates the correct worksheet

Range(“A5”).End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteAllUsingSourceTheme ‘ finds the first available cell in the worksheet and pastes the data

Results.Close ‘Closes the results spreadsheet
Application.ScreenUpdating = True ‘turns back on the screen updating

You should just be able to copy and paste the code obviously updating the path information for your own spreadsheets you may need to alter some cell information also.

Give me a shout if yo get stuck with anything

Cheers

Matthew

The following users say thank you to Matthew for this useful post:

jonnyhammer, jb8426, bettyblogger
Avatar
ArtTest
Moderator
Moderators

Members
Forum Posts: 259
Member Since:
March 6, 2016
sp_UserOfflineSmall Offline
9
May 5, 2020 - 6:26 pm
sp_Permalink sp_Print
+1

Matthew,

you could easily add the macro names using Call xxxx I think and have one click do it all for you!

Create a new macro to call each strategy macro in turn then Copy_Current_Games then next strategy macro then Copy_Current_Games, rince and repeat.

The following users say thank you to ArtTest for this useful post:

jonnyhammer
Avatar
Matthew
Beginner
Members
Forum Posts: 13
Member Since:
February 23, 2019
sp_UserOfflineSmall Offline
10
May 5, 2020 - 6:46 pm
sp_Permalink sp_Print
+1

I was thinking of that Art at the minute I’m only running 4 strategies so it’s not a big deal but I can see how much of a time saver it would be if you had 20/30 strategies 

cheers

Matthew

Avatar
mrwhitney
Beginner
Members
Forum Posts: 43
Member Since:
September 12, 2019
sp_UserOfflineSmall Offline
11
May 7, 2020 - 1:23 pm
sp_Permalink sp_Print
0

Thanks Art and Matthew,

I’ll give these macros a try and look forward to using them once things settle down. I’m going to carry on printing out and ticking off selections to double check before I place bets but I expect this will speed the process up quite a bit.

Avatar
jonnyhammer
Beginner
Members
Forum Posts: 7
Member Since:
July 12, 2018
sp_UserOfflineSmall Offline
12
May 8, 2020 - 12:18 pm
sp_Permalink sp_Print
0

To be honest this scares the wotsits out of me but will give it a try with a copy of my database (in case i kill the main one), thanks guys for posting these 

JonnyH

Avatar
leewillo75
Beginner
Members
Forum Posts: 93
Member Since:
July 9, 2019
sp_UserOfflineSmall Offline
13
May 12, 2020 - 3:50 pm
sp_Permalink sp_Print
0

ArtTest said
Yes, but it requires some VBA knowledge. It is perfectly possible to get 1 macro to run others and to collate and display results, but this is not a task for a VBA beginner.

  

art is there any simple way to use dates in bloody VBA ??

For example if you search say specific dates its amercian format and then say you want to run a macro say this day plus 5 in vba format its example 03-Jan-2020.

I am trying to make it dynamic to store the date as a variable so when you change the date and click the button it runs the stored variable date.

Only reason I ask is during lockdown and with Chrissy’s help I have really got into VBA and doing things now 3 months ago I didnt think was possible without forumlas.

Trying to automate the whole ultimate process and I have got all the macros running together but having a problem when it comes to selecting fixtures on a certain date and copying into my own fixture list

Hope you dont mind me asking

Thanks

Lee

Avatar
ArtTest
Moderator
Moderators

Members
Forum Posts: 259
Member Since:
March 6, 2016
sp_UserOfflineSmall Offline
14
May 12, 2020 - 5:38 pm
sp_Permalink sp_Print
+1

Check out FORMAT

for example 

Format(Date, "dd/mm/yyyy")

The following users say thank you to ArtTest for this useful post:

leewillo75
Avatar
leewillo75
Beginner
Members
Forum Posts: 93
Member Since:
July 9, 2019
sp_UserOfflineSmall Offline
15
May 12, 2020 - 6:06 pm
sp_Permalink sp_Print
0

thanks alot art for the replySmile

Avatar
leewillo75
Beginner
Members
Forum Posts: 93
Member Since:
July 9, 2019
sp_UserOfflineSmall Offline
16
May 15, 2020 - 2:54 pm
sp_Permalink sp_Print
0

ArtTest said
Check out FORMAT

for example 

Format(Date, "dd/mm/yyyy")

  

Art, I cant thank you enough mate that has sorted out all my date issues re running stuff on auto for search criteria with dynamic values

Avatar
Matthew
Beginner
Members
Forum Posts: 13
Member Since:
February 23, 2019
sp_UserOfflineSmall Offline
17
July 7, 2020 - 8:03 pm
sp_Permalink sp_Print
0

Just thought I would add some code as an update to the code I posted earlier in this thread. This will select and copy the data based on a date you input into your sheet.

Dim MasterSheet As Workbook
Dim CurrentWorkbook As Workbook
Dim FoundCell As Range
Dim MyDt As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set CurrentWorkbook = ThisWorkbook
MyDt = Worksheets(“FTS Master Odds Sheet”).Cells(6, 4).Value Sets MyDT to the date value I have entered

Set MasterSheet = Workbooks.Open(“C:\Users\mjdav\Documents\FTS\Master Sheet\MasterSheet.xlsm”, True, True)

Set FoundCell = Range(“B:B”).Find(what:=DateValue(MyDt), LookIn:=xlFormulas) ‘Finds the cell based on the date I entered and copies the data to the end of the sheet
FoundCell.Offset(0, -1).Select
Range(ActiveCell, ActiveCell.End(xlDown).Range(“BH4”)).Copy

ThisWorkbook.Activate
Set FoundCell = Range(“B:B”).Find(what:=DateValue(MyDt), LookIn:=xlFormulas)
FoundCell.Offset(0, -1).PasteSpecial Paste:=xlPasteAllUsingSourceTheme

MasterSheet.Close
Application.ScreenUpdating = True
Application.DisplayAlerts = True

If anybody needs some help customising for their own needs give me a shout

Cheers

Matthew

Avatar
ArtTest
Moderator
Moderators

Members
Forum Posts: 259
Member Since:
March 6, 2016
sp_UserOfflineSmall Offline
18
July 7, 2020 - 9:14 pm
sp_Permalink sp_Print
0

Good, but be careful of this line

 

Set MasterSheet = Workbooks.Open(“C:\Users\mjdav\Documents\FTS\Master Sheet\MasterSheet.xlsm”, True, True)

 

Folks will need to set the text in bold to where they store the MasterSheet.xlsm

Avatar
jonnyhammer
Beginner
Members
Forum Posts: 7
Member Since:
July 12, 2018
sp_UserOfflineSmall Offline
19
August 4, 2020 - 11:49 am
sp_Permalink sp_Print
0

Ive tried several times to follow the instructions in this thread and get all my selections into one sheet quickly. In doing so i seem to have ruined three copies of my systems spreadsheet. Fortunately i tried it on copies …I just dont have any more thasn basic excel knowledge and this vbs stuff is clearly beyond me

Would anyone be willing or able to do this for me if i send them my sytstem sheet, i dont mind sharing the systems I have got, theyre not rocket science but i know im nver going to be able to do this on my own and i am missing too many selections by not haveing time to run all the macros and copy the bets to a spreadsheet.

I usually paste all the selections into bf bot manager as RTS_Flat, RTS_Mixed or RS_NH where i have the scripts set up to bet to individual price ranges and betting banks. The macros are separated the same way on my spreadsheet and colour coded accordingly. I usuallu paste the days runners into a blank sheet with the macros in and run each indivcidually, before copying an psting into a separate spreadshhet which i then convert to csv with those three system names for bfbot.

If anyone is able to help that would be awesome and be a game changer for RTS raciong for me

Cheers guys

Jonny

Avatar
ArtTest
Moderator
Moderators

Members
Forum Posts: 259
Member Since:
March 6, 2016
sp_UserOfflineSmall Offline
20
August 4, 2020 - 5:26 pm
sp_Permalink sp_Print
+1

Sure send it over to arthur_ockwell@hotmail.com

The following users say thank you to ArtTest for this useful post:

jonnyhammer
Forum Timezone: Europe/London
Most Users Ever Online: 92
Currently Online: combo1956, jamiep, Matt, akyne1
Guest(s) 1
Currently Browsing this Page:
1 Guest(s)
Top Posters:
Lord Dio: 122
leewillo75: 93
@capitalchrisw: 83
des911: 67
Callum: 65
Neil: 56
jamiep: 50
gurds1991: 48
jb8426: 48
chasanddave: 48
Newest Members:
11percent
sheilaki3
chrise10
akyne1
melissajr3
trueblue2901
gordiecruyff
stefanieow16
andrebb1
eato81
Forum Stats:
Groups: 5
Forums: 24
Topics: 268
Posts: 2144

 

Member Stats:
Guest Posters: 3
Members: 4696
Moderators: 2
Admins: 2
Administrators: Ian, Geeky Boy
Moderators: ArtTest, davefts