Help with loading markets at midnight

[samhayer]

I need some help please, I would like my exel sheets which are linked to tabs in BA and are working okay to load new markets at midnight . I have been trying to do this for ever and it just wont work for me . I have downloaded Gary's auto loading sheet still not working. Please some tell me what I am doing wrong . I paste this code in sheet 1 this is the market sheet
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count = 16 Then
Application.EnableEvents = False
If triggerQuickPickListReload Then
triggerQuickPickListReload = False
Range("Q2").Value = -3.7
triggerFirstMarketSelect = True
Else
If triggerFirstMarketSelect Then
triggerFirstMarketSelect = False
Range("Q2").Value = -5
End If
End If
Application.EnableEvents = True
End If
End Sub

and this in the workbook

Option Explicit

Private Sub Workbook_Open()
Application.OnTime TimeValue("00:00:30"), "loadQuickPickList"
End Sub

and this in Module1

Public triggerQuickPickListReload As Boolean
Public triggerFirstMarketSelect As Boolean

Public Sub loadQuickPickList()
triggerQuickPickListReload = True
Application.OnTime TimeValue("00:00:30"), "loadQuickPickList"
End Sub

and I have tried lots of different ways just cant get it wright can some one please help me thanks

[summit]

Hi,

I hope this helps. It 100% works for my.

I am not sure what you are coding but I use the fact the cell Q2 does all the work and have a simple macro running which loads the markets at the time specified.

This macro refreshes the market at n11 minutes past 1am and the sets the first market at 12 minutes past 1am.

It then refreshes the market using -6 in cell Q2 every 30 seconds

You can change the times and timings to suit your requirements.
--------------------------------------------------
Dim RunWhen As Date
Dim H As Integer
Dim m As Integer

Sub DogGru() "put the name of your macro here and change accordingly where you see DogGru() below"

Application.ScreenUpdating = False

With Sheet10 "Change to your sheet number"
H = Hour(Now)
m = Minute(Now)
If (H = 1 And m = 11) Then
.Cells(2, 17).Value = -3
ElseIf (H = 1 And m = 12) Then
.Cells(2, 17).Value = -5
Else
.Cells(2, 17).Value = -6
End If
RunWhen = Now + TimeValue("00:00:30")
Application.OnTime RunWhen, "DogGru"
End With

Application.ScreenUpdating = True

End Sub
Sub StopDogGru()
On Error Resume Next
Application.OnTime EarliestTime:=RunWhen, Procedure:="DogGru", Schedule:=False
End Sub



You are welcome

Steve

[summit]

Part two

Remember, you can only change one market. So you can only refresh one market - usually the one that is on the quick pick list.

Get back if any problems.

I am no expert, but I do understand this small macro.

Steve

[samhayer]

Good Morning Steve thanks for your reply I will try what yo advise and I will let you know how I get on, thanks very much you have a nice day Sam Hayer