Resetting the counter on my button

Question

So I have a search button in "Sheet1" that searches through 10,000 IDs and when it finds the right one, it pastes it in "Sheet2". The problem I am having is that I don't know how to reset the counter on my button. I want to be able to search and paste around 4-5 IDs, then be able to start at the top of "Sheet2" again, but right now it just keeps going down and down. I will attach the code I have below. Any help would be greatly appreciated.

Private Sub CommandButton1_Click()

Dim search_value As String

Dim loop_rows As Integer

Dim loop_i As Integer

Dim sheet_search As String

Dim sheet_paste As String

sheet_search = "Sheet1"

sheet_paste = "Sheet2"

search_value = TextBox1.Value

loop_rows = Sheets(sheet_search).UsedRange.Rows.Count

Sheets(sheet_search).Select

For loop_i = 1 To loop_rows

    If Sheets(sheet_search).Cells(loop_i, 1) = search_value Then

         Sheets(sheet_search).Rows(loop_i).Copy

         Sheets(sheet_paste).Select

        If IsNull(Sheets(sheet_paste).Cells(2, 1)) Then

            Sheets(sheet_paste).Rows(2).Select

            ActiveSheet.Paste

         Else

 '             ActiveSheet.UsedRange.Rows(UBound      (ActiveSheet.UsedRange.Value)).Row

         Sheets(sheet_paste).Rows(Sheets(sheet_paste).UsedRange.Rows(UBound(ActiveSheet.UsedRange.Value)).Row + 1).Select

         ActiveSheet.Paste

         End If

    End If

'loop_i = loop_i + 1

Next

End Sub

Thanks again

You are finding the last Row and pasting. Using a function that should be avoided: https://stackoverflow.com/questions/21172108/activesheet-usedrange-columns-count-8-what-does-it-mean — danieltakeshi, Jun 19 '17 at 20:31

Resetting the counter on my button

0 Answers0