CODEWITHSUNDEEP
excelvba
SeT
SeT

Reputation: 224

Is it possible to repeat a specific operation twice but only on Mondays?

I need help with the below macro.

I run a macro for my report on a daily basis. But only on Mondays, I need to repeat the below operations two times more(3 times altogether):

Range("B8").End(xlToRight).Offset(, 0).Select
Selection.Copy
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste

Range("B9").End(xlToRight).Offset(, 0).Select
Selection.Copy
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste

I'm guessing that I need to use the Weekday(Now()) = vbMonday statement but not 100% sure how to use it with the above code.

Thanks in advance!

Upvotes: 2

Views: 79

Answers (1)

Vityata
Vityata

Reputation: 43575

There are at least two ways to do it. The first one is to make it a separate Sub and to call it explicitly 3 times on Monday.

Sub TestMe()

    If Weekday(Now()) = vbMonday Then
        SomeSelection
        SomeSelection
        SomeSelection
    End If

End Sub

Sub SomeSelection()
    'OP Code
End Sub

The second one is to make a loop and to make sure it repeats 3 times on Monday:

Sub TestMe()

    Dim repeater As Long: repeater = 1
    If Weekday(Now()) = vbMonday Then repeater = 3

    Dim counter As Long
    For counter = 1 To repeater
        Range("B8").End(xlToRight).Offset(, 0).Select
        'OP code...
        ActiveSheet.Paste
    Next

End Sub

Upvotes: 1

Related Questions