Please Help me Review Code - LOOP doesn't stop
 

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

Please Help me Review Code - LOOP doesn't stop
 

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


--
Hope that helps.

Vergel Adriano


"Filo" wrote:

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

Please Help me Review Code - LOOP doesn't stop
 

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.

--
Don Guillett
SalesAid Software

"Filo" wrote in message
...
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

Please Help me Review Code - LOOP doesn't stop
 

Thank you!

"Vergel Adriano" wrote:

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


--
Hope that helps.

Vergel Adriano


"Filo" wrote:

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