none
VBA to run a subroutine on a specific day of the week

Answers

  • Have you already set up so many macros .It is not required to setup so many macros.

    The following will do it in a single macro. I assume if first week then in B3 if 2nd week then c3 in this way you want the macro to run.

    I have developed the routine on the basis of a Function of Excel WeekNum which returns the week num (Within 52 ) on the date specified.

    The [WeekNum(Now())] returns current week number.[Pls do a bit research on the function]

    Sub updateMe()
        If Weekday(Now()) = vbThursday Then
            Worksheets("Sheet1").Cells([WeekNum(Now())] + 1, 3).Formula _
              = Sheets("source data wk1").Range("c1").Value
            MsgBox "Today is Thursday"
        End If
    End Sub

    Last of all:Do you want the macro to run automatically on Thursday.Then let me know I shall try to help you.

    ---------------------------------------------------------------------------------------------

    Please do not forget to click “Vote as Helpful” if any post helps you and mark as Answer if it solves the issue.


    • Marked as answer by zakynthos Thursday, June 21, 2012 2:54 PM
    Thursday, June 21, 2012 2:38 PM
  • "Also, why is there a MsgBox - can it be omitted? " -

    I just missed to delete it.I am giving a stepwise short explanation of the code.

    1.The Now function takes no argument and returns the current date and time (22th Jun 2012,11:25 Pm )

    2.The Weekday function takes a date as argument and returns the day of week(vbSunday for Sunday,vbMonday for Monday.

    3.The If statement checks the return of Weekday with the spceified day.(I used vbThursday).If true then executes your code  otherwise skips the code.

    4.On the basis of week number your column number changes.So I used WeekNum function which takes date as argument and returns the no of week.It is an excel function.I wrapped it in [] so that it is interpreted as entered in excell cell.VBA has no equivalent function.

    5.The cells object takes row and column as number and returns the range.It is better than Range method because it uses number for column and row.In the assisgnemnet I took the return value of WeekNum function and used as argument of Cells.

    Hope I have been able make it a bit clear.

    For details of functions used pls refer the offline help.

    ---------------------------------------------------------------------------------------------

    Please do not forget to click “Vote as Helpful” if any post helps you and mark as Answer if it solves the issue.

    • Marked as answer by zakynthos Saturday, June 23, 2012 9:43 AM
    Friday, June 22, 2012 5:59 AM
  • Insert at the top

    If Weekday(Now()) = vbThursday Then

    'Replace msgbox with your entire code.

    'Chnage the vbThursday to naother day as you need

    'vbSunday etc. MsgBox "Today is Thursday" End If


    • Marked as answer by zakynthos Thursday, June 21, 2012 12:13 PM
    Thursday, June 21, 2012 9:58 AM

All replies

  • Insert at the top

    If Weekday(Now()) = vbThursday Then

    'Replace msgbox with your entire code.

    'Chnage the vbThursday to naother day as you need

    'vbSunday etc. MsgBox "Today is Thursday" End If


    • Marked as answer by zakynthos Thursday, June 21, 2012 12:13 PM
    Thursday, June 21, 2012 9:58 AM
  • Hi,

    Many thanks for your help - much appreciated

    Thursday, June 21, 2012 12:14 PM
  • Hi,

    I hope you don't mind one more question on this.  The code I need to run on this is a weekly update to a summary sheet, for

    weeks 1 to week 52

    If next week was week run, I would run:

    sub updateWk1()

    With Worksheets("Sheet1")  
       .Range("B3").Formula = Sheets("source data wk1").Range("c1").Value

    etc etc

    End sub

    What I would like to be able to do is run the report weekly on a given day, say Thursday, such that on Week2 Thursday, this runs

    sub updateWk2()

    With Worksheets("Sheet1")  
       .Range("C3").Formula = Sheets("source data wk1").Range("c1").Value

    etc etc

    End sub

    until

    sub updateWk52()

    With Worksheets("Sheet1")  
       .Range("BA3").Formula = Sheets("source data wk1").Range("c1").Value

    etc etc

    End sub

    Many thanks

    Thursday, June 21, 2012 2:00 PM
  • Have you already set up so many macros .It is not required to setup so many macros.

    The following will do it in a single macro. I assume if first week then in B3 if 2nd week then c3 in this way you want the macro to run.

    I have developed the routine on the basis of a Function of Excel WeekNum which returns the week num (Within 52 ) on the date specified.

    The [WeekNum(Now())] returns current week number.[Pls do a bit research on the function]

    Sub updateMe()
        If Weekday(Now()) = vbThursday Then
            Worksheets("Sheet1").Cells([WeekNum(Now())] + 1, 3).Formula _
              = Sheets("source data wk1").Range("c1").Value
            MsgBox "Today is Thursday"
        End If
    End Sub

    Last of all:Do you want the macro to run automatically on Thursday.Then let me know I shall try to help you.

    ---------------------------------------------------------------------------------------------

    Please do not forget to click “Vote as Helpful” if any post helps you and mark as Answer if it solves the issue.


    • Marked as answer by zakynthos Thursday, June 21, 2012 2:54 PM
    Thursday, June 21, 2012 2:38 PM
  • Hi,

    Thanks - this is just the answer I was hoping for.

    No, only examples of what I want to run - I was hoping that by asking the question I could avoid this step!

    Not sure about which day of week yet, as not been decided which day report will run on so what would be really useful is to understand how the code varies if say I want to run on a Monday, Tuesday - or any other weekday.

    Also, why is there a MsgBox - can it be omitted?

    Thursday, June 21, 2012 3:15 PM
  • "Also, why is there a MsgBox - can it be omitted? " -

    I just missed to delete it.I am giving a stepwise short explanation of the code.

    1.The Now function takes no argument and returns the current date and time (22th Jun 2012,11:25 Pm )

    2.The Weekday function takes a date as argument and returns the day of week(vbSunday for Sunday,vbMonday for Monday.

    3.The If statement checks the return of Weekday with the spceified day.(I used vbThursday).If true then executes your code  otherwise skips the code.

    4.On the basis of week number your column number changes.So I used WeekNum function which takes date as argument and returns the no of week.It is an excel function.I wrapped it in [] so that it is interpreted as entered in excell cell.VBA has no equivalent function.

    5.The cells object takes row and column as number and returns the range.It is better than Range method because it uses number for column and row.In the assisgnemnet I took the return value of WeekNum function and used as argument of Cells.

    Hope I have been able make it a bit clear.

    For details of functions used pls refer the offline help.

    ---------------------------------------------------------------------------------------------

    Please do not forget to click “Vote as Helpful” if any post helps you and mark as Answer if it solves the issue.

    • Marked as answer by zakynthos Saturday, June 23, 2012 9:43 AM
    Friday, June 22, 2012 5:59 AM
  • Hi,

    Yes, you've made it perfectly clear - an excellent, concise answer, thanks so much  - I did take up your suggestion to research the Excel WeekNum.  This will save what would have been wasted time and effort on replicating code or referencing multiple rows of cells.

    Also your comments about 'cells object number' made me re-think other formulas and functions I'd used elsewhere in my code for the data analysis and as a result have saved myself about 3 days work!

    Much appreciated!

    Saturday, June 23, 2012 9:51 AM