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:=(.Wor ksheets("STD Calc") _
.Range("C6")), LookAt:=xlWhole, LookIn:=xlFormulas)
If rFound Is Nothing Then
Set rCell =
..Worksheets("Data").Range("A65536").End(xlUp).Off set(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!!!!

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" wrote in message
...
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:=(.Work sheets("STD Calc") _
.Range("C6")), LookAt:=xlWhole, LookIn:=xlFormulas)
If rFound Is Nothing Then
Set rCell =
.Worksheets("Data").Range("A65536").End(xlUp).Offs et(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,

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


"NickHK" wrote:

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" wrote in message
...
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:=(.Work sheets("STD Calc") _
.Range("C6")), LookAt:=xlWhole, LookIn:=xlFormulas)
If rFound Is Nothing Then
Set rCell =
.Worksheets("Data").Range("A65536").End(xlUp).Offs et(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!!!!

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

"TimN" ...
NickHK,

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


"NickHK" wrote:

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" wrote in message
...
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:=(.Work sheets("STD Calc") _
.Range("C6")), LookAt:=xlWhole, LookIn:=xlFormulas)
If rFound Is Nothing Then
Set rCell =
.Worksheets("Data").Range("A65536").End(xlUp).Offs et(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!!!!