2

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!

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
SeT
  • 224
  • 2
  • 13
  • 2
    You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – Pᴇʜ Jan 22 '19 at 09:27
  • Thanks for advice Pᴇʜ, I will do so, just need to find a bit of spare time within the next few days :) – SeT Jan 22 '19 at 12:11

1 Answers1

1

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
Vityata
  • 42,633
  • 8
  • 55
  • 100
  • 1
    I made a loop as advised above and it works perfectly. Thanks Vityata – SeT Jan 22 '19 at 11:05
  • 1
    @SeT - welcome. As a next step, try to refactor the whole code, avoiding select, as mentioned in the comment in the question - it would be of use, if you invest a few hours trying to do it - [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – Vityata Jan 22 '19 at 11:08
  • 1
    Thanks Vityata, will definitely recreate my code within the next few days, just need to find a bit of spare time to do so :) – SeT Jan 22 '19 at 12:10