daily run-code included

[bonovox1]

hi i want to run this macro daily; so the name of the excel file john.xls changes daily; what do i need to put in my code to allow it to run.

here is my code;

Sub DAILY_RUN()
'
' DAILY_RUN Macro
  Application.CutCopyMode = False
    Workbooks.Open Filename:="M:\Reports\SVP1-WFR.csv"
    Range("B2:C39").Select
    Selection.Copy
    Windows("john.xls").Activate
    Sheets("EUR").Select
    Range("X2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("SVP1-WFR.csv").Activate
    ActiveWindow.SmallScroll Down:=-18
    Range("E2:F39").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("john.xls").Activate
    Sheets("GBP").Select
    Range("AB2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("SVP1-WFR.csv").Activate
    ActiveWindow.SmallScroll Down:=-15
    Range("H2:I40").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("john.xls").Activate
    Sheets("USD").Select
    Range("Z2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 15
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Range("R2:R26").Select
    Application.CutCopyMode = False
    Selection.NumberFormat = "0"
    Sheets("EUR").Select
    Range("R2:R25").Select
    Selection.NumberFormat = "0"
    Sheets("GBP").Select
    Range("T2:T28").Select
    Selection.NumberFormat = "0"
    
End Sub

EDIT: added code tags
NOTE: please only hit the "SUBMIT" button 1x
Moderator

 

[venkat1926]

you have another sub with a code statement something ike this

Application.OnTime TimeValue("8:01:00"), "DAILY_RUN"

change the time to suit you .

run this macro as you as you open for the day.

alternatively put this code statement (ontime code) in an event code


Private Sub Workbook_Open()
Application.OnTime TimeValue("8:00:00"), "DAILY_RUN"
End Sub

so that as soon as you open the file the ontime code will run and at the proper time the sub "DAILY_RUN" will run at 8:00:00.

try one or two experiments. I have not much used ontime code.
see excel vba help keyword "ontime"

 

[jindon]

try
to ThisWorkbook module

Private Sub Workbook_Open()
If ThisWorkbook.Sheets(1).Range("a1").Value = Date Then Exit Sub
MsgBox "Last updated on " & ThisWorkbook.Sheets(1).Range("a1").Value
DAILY_RUN
End Sub

Private Sub Before_Save(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ThisWorkbook.Sheets(1).Value = Date
End Sub

Sub DAILY_RUN()
  Dim wsCSV As Worksheet, wsEUR As Worksheet
  Dim wsGBP As Worksheet, wsUSD As Worksheet
  Application.CutCopyMode = False
    Set wsCSV = Workbooks.Open("M:\Reports\SVP1-WFR.csv").Sheets(1)
    With Workbooks("john.xls")
         Set wsEUR = .Sheets("EUR")
         Set wsGBP = .Sheets("GBP")
         Set wsUSD = .Sheets("USD")
    End With
    With wsCSV.Range("B2:C39")
         wsEUR.Range("x2").Resize(.Rows.Count, .Columns.Count).Value = .Value
    End With
    With wsCSV.Range("e2:f39")
         wsGBP.Range("ab2").Resize(.Rows.Count, .Columns.Count).Value = .Value
    End With    
    With wsCSV.Range("h2:i140")
         wsUSD.Range("z2").Resize(.Rows.Count,.Columns.Count).Value = .Value
    End With
    wsUSD.Range("R2:R26").NumberFormat = "0"
    Sheets("EUR").Select
    wsEUR.Range("R2:R25").NumberFormat = "0"
    wsGBP.Range("T2:T28").NumberFormat = "0"
    
End Sub

 

[bonovox1]

hi, i tried the above but none seem to work. for the previous suggestion i get a synax error for this

Set wsCSV = Workbooks.Open(K:\VerasisReports\SVP1-EREISWFR.csv").Sheets(1).
Any other suggestions?

 

[jindon]

hi, i tried the above but none seem to work. for the previous suggestion i get a synax error for this

Set wsCSV = Workbooks.Open(K:\VerasisReports\SVP1-EREISWFR.csv").Sheets(1).
Any other suggestions?

Missing "

Set wsCSV = Workbooks.Open("K:\VerasisReports\SVP1-EREOSWFR.csv").Sheets(1)

 

[bonovox1]

hi, there is a syndax error in the following
Workbooks.Open Filename:="M:\Reports\SVP1-WFR.csv"

Please ignore my last comment. Any ideas; please help cause this is driving me mad. I have the fily saved with todays date and then i run it, just to be clear.

 

[jindon]

hi, there is a syndax error in the following
Workbooks.Open Filename:="M:\Reports\SVP1-WFR.csv"

Please ignore my last comment. Any ideas; please help cause this is driving me mad. I have the fily saved with todays date and then i run it, just to be clear.

If you get a Syntax Error on that line, nobody actually can do anything.
Just rung the code and read the message

Sub test2()
Dim myDir As String, myCSV As String
myDir = Dir("M:\Reports",vbDirectory)
If myDir = "" Then
     MsgBox "Wrong path" : Exit Sub
End If
myCSV = Dir(myDir & \SVP1-WFR.csv", vbNormal)
MsgBox IIf(mySCV="","Wrong file name","Path & FileName are OK")
End Sub