Please Help me Review Code - LOOP doesn't stop

[Guest]

Hello- Could you please help me review what I am writing wrong in the code
below? The problem I want to solve is that after the data is pasted, the
program keeps on looping and it ends up deleting the correct information that
it had initially pasted. I would like the program to stop after it pastes the
information once. What do I need to change in the code to accomplish that?
Thank you.

Filo
------------------------------------------
Sub InsertDTBinMFR()


Dim DeptName As String, wb As String
Dim z As Long, x As Long

wb = Workbook.Name

Workbooks(wb).Activate
Sheets(1).Select

Worksheets.Add Count:=1, After:=Sheets(Sheets.Count)
ActiveSheet.Name = "MTD-DTB"
DeptName = Left(Sheets(1).Range("F1").Text, 5)


Workbooks("drill testing.xls").Activate
Sheets("DTB").Select

For z = 1 To 20000

If Cells(z, "a").Value = DeptName Then

Firstrow = Range("A" & z).Address

For x = 20000 To 1 Step -1

If Range("A" & x).Value = DeptName Then
lastrow = Range("I" & x).Address

Range(Firstrow, lastrow).Select
Selection.Copy

Workbooks(wb).Activate
Sheets(2).Select
Range("A1").Select

Selection.PasteSpecial Paste:=xlValues
End If

Next x

End If

Next z

End Sub

 

[Guest]

Filo,

Exit out of the For loop on the line after the paste operation. i.e.

Selection.PasteSpecial Paste:=xlValues
Exit For '<---insert this line to exit out of the current for loop

 

[Don Guillett]

Tell us, from the beginning, what you are trying to do.
How is z determined? Once both wbs are active you do NOT have to select to
copy from one to the other.

 

[Guest]

Thank you!

Filo,

Exit out of the For loop on the line after the paste operation. i.e.

Selection.PasteSpecial Paste:=xlValues
Exit For '<---insert this line to exit out of the current for loop