Over-Eager code is pasting too many times

[Stuart]

The code is running on the activeworkbook, where data is in
rows A:G. An identifier for the provisional start of each
record is in col A (defined as "A" & Cell.Row). User can
identify records to be copied using A, B , C etc in cols I to J
in Cell.Row
So, having found a record ("A" & Cell.Row) I now check to
see if the range(("H" & Cell.Row, "J" & Cell.Row) contains
any user tags, and if so, then copy the record to the destination
Workbooks("Sorted_Tagged " & x(4) & ".xls"). _
Worksheets(TagCell.Value) for each tag, present.

Here's current code:

For Each TagCell In .Range("H" & Cell.Row, "J" & Cell.Row) _
.SpecialCells(xlConstants)
If Not IsEmpty(TagCell) Then
.Range("A" & StartCopyRow, "J" & EndCopyRow).Copy _
Destination:=Workbooks("Sorted_Tagged " _
& x(4) & ".xls").Worksheets(TagCell.Value) _
.Range("B65536").End(xlUp).Offset(2, -1)
If Not IsEmpty(.Range("F" & StartCopyRow).End(xlDown) _
.Offset(-1, -5)) Then
.Range("F" & StartCopyRow).End(xlDown) _
.Offset(-1, -5).Copy _
Destination:=Workbooks _
("Sorted_Tagged " & x(4) & ".xls"). _
Worksheets(TagCell.Value) _
.Range("A65536").End(xlUp).Offset(0, 10)
End If
End If
Next

All is fine except that if user has tagged all 3 cols against a
record, then I'm getting 3 copies of the record pasting into each
of the 3 destination sheets(g).

Would be very grateful for help in explaining this, please.


Regards.

 

[J.E. McGimpsey]

If I understand you correctly, if Hx:Jx all have tags, then the
For...Net loop will loop three times. To get it to stop after
finding the first, put

Exit For

before the second End If.