have a workbook_open macro run every 5 times and not every time

[still learning]

Hi,

I use this when a workbook opens

Private Sub Workbook_open()
Sheets("sheet1").Select
movebox
opening
End Sub

The macro “movebox” goes to the range “loans” and moves it down a line

Sub movebox()
Range("loans").Select
Application.CutCopyMode = False
Selection.Cut
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
Range("A1").Select
End Sub

Is there a way to only run this after the wookbook opens 5 times and not every time

mike

 

[Peter_SSs]

Test this with a new workbook.
Include a worksheet called "Count". You can hide that worksheet if you want.
Then include this workbook open code.

If it does what you want then you can replace the message box line with whatever code you want to run each 5th time.

Private Sub Workbook_Open()
  With Sheets("Count").Range("A1")
    If .Value = 4 Then
      .Value = 0
      MsgBox "5th time"
    Else
      .Value = .Value + 1
    End If
  End With
  ThisWorkbook.Save
End Sub

 

[still learning]

Hi Pete
I get a "type mismatch" error at this line (run time error 13)

.Value = .Value + 1

mike

 

[Peter_SSs]

What is in A1 of sheet 'Count'?
That cell should have been empty when you created the sheet and this code only puts numbers in it so should be no type mismatch.
Testing worked fine at my end.

 

[still learning]

Hi Pete
I had made a test macro to see if I was putting it in the right place in your macro. It went to A1 and typed "mike". Every time i saved it, it stayed.
I fixed that problem and it worked perfect.
Guess I should learn to read better. I didn't pay attention to the ....Range("A1")
Thank you


mike

 

[Peter_SSs]

I fixed that problem and it worked perfect.

Thank you

You're welcome. Glad you got it sorted.