How to trigger MsgBox for this event?

[TimN]

I have the following code which copies a range from one worksheet to another,
both within the same workbook. If there is a duplicate date it copies over
the date, if new date it copies to the next available cell.

Dim rCell As Range
Dim rFound As Range
With Application.ThisWorkbook
Set rFound =
..Worksheets("Data").Columns("B").Find(What:=(.Worksheets("STD Calc") _
.Range("C6")), LookAt:=xlWhole, LookIn:=xlFormulas)
If rFound Is Nothing Then
Set rCell =
..Worksheets("Data").Range("A65536").End(xlUp).Offset(1, 0)
Else
Set rCell = rFound.Offset(-3, -1)
End If
Worksheets("STD Calc").Range("B17:Q37").Copy
rCell.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
End With
Dim RetVal As Variant
RetVal = Application.GetSaveAsFilename(Range("C2"))

If RetVal <> False Then
ThisWorkbook.SaveAs RetVal & "xls"

End If

This copies two weeks of data at a time. on the 12th week or 6th iteration,
a maximum is reached. I want at that point to cause a MsgBox to pop up
indicating can't use the form go to form blah, blah.... and on OK the user
form is closed.

I think I know how to do the MsgBox itself. My question is how do I trigger
the MsgBox to appear? I know the cell in the worksheet where data is being
copied to that would represent the maximum. Would that be the place that
triggers the MsgBox? If so, how do I accomplish that?

As always thanks so much for your help!!!!

 

[NickHK]

There's no loop in this code, so I presume this is a routine repetitively
called. If so you need a module level variable (or a Static variable in this
routine, see the VBA Help)
Dim/Static RoutineCounter As Long

In the routine, increment on entry
RoutineCounter=RoutineCounter+1

If RoutineCounter Mod 6=0 Then MsgBox "This a multiple of 6"

NickHK

 

[TimN]

NickHK,

I don't understand the module level variable or Static variable. can you
explain further?

 

[NickHK]

Because you need to maintain the value of RoutineCounter between calls to
your code, RoutineCounter would normally be declared poutside of the
routine, at the top of the module.
Alternatively, you can use
Static RoutineCounter as Long
in the routine

NickHK