how do i move this sub from sheet 1 to sheet 2

Question

I need to move this search to a userform that currently performs a search on sheet 1 I would like to put the database on sheet 2, everything works but the insert function cannot find the last row.

Private Sub ComboBox1_Change()
Dim Riga As Long
    If ComboBox1.Value <> "" Then
        Riga = ComboBox1.ListIndex + 2
        Cells(Riga, 1).Select
        
        TextBox1 = ComboBox1.Value
        TextBox2 = Sheets(2).Cells(ActiveCell.Row, 2)
        TextBox3 = Sheets(2).Cells(ActiveCell.Row, 3)
        TextBox4 = Sheets(2).Cells(ActiveCell.Row, 4)
        TextBox5 = Sheets(2).Cells(ActiveCell.Row, 5)
        TextBox6 = Sheets(2).Cells(ActiveCell.Row, 6)
    End If
End Sub

Private Sub CommandButton_Chiudi_Click()
    Unload Me
End Sub

Private Sub CommandButton_Nuovo_Click()
Dim tx As Byte
    ComboBox1.Value = ""
    Cells(Range("A1").End(xlDown).Row + 1, 1).Select
    For tx = 1 To 6
        Userform1.Controls("TextBox" & tx).Text = ""
    Next tx
    TextBox1.SetFocus
End Sub
Private Sub CommandButton_Cancella_Click()
Dim Messaggio As String, Stile As String, Titolo As String
Dim Risposta As VbMsgBoxResult

    Titolo = "Cancellazione Record."
    Messaggio = "Vuoi cancellare il tesserino:" & Chr(10) & Chr(10) _
    & Sheets(2).Cells(ActiveCell.Row, 1) & Chr(10) _
    & Sheets(2).Cells(ActiveCell.Row, 2) & Chr(10) _
    & Sheets(2).Cells(ActiveCell.Row, 3) & Chr(10) _
    & Sheets(2).Cells(ActiveCell.Row, 4) & Chr(10) _
    & Sheets(2).Cells(ActiveCell.Row, 5) & Chr(10) _
    & Sheets(2).Cells(ActiveCell.Row, 6) & Chr(10)
    Stile = vbYesNo + vbQuestion + vbDefaultButton2
    Risposta = MsgBox(Messaggio, Stile, Titolo)
    If Risposta = vbNo Then End
    ActiveCell.EntireRow.Delete
    End
End Sub

Private Sub CommandButton_Aggiorna_Click()
Dim Riga As Long
Dim Messaggio As String, Stile As String, Titolo As String
Dim Risposta As VbMsgBoxResult

    Titolo = "Cancellazione Record."
    Messaggio = "Vuoi registrare il tesserino:" & Chr(10) & Chr(10) _
    & Sheets(2).Cells(ActiveCell.Row, 1).Value & Chr(10) _
    & Sheets(2).Cells(ActiveCell.Row, 2).Value & Chr(10) _
    & Sheets(2).Cells(ActiveCell.Row, 3).Value & Chr(10) _
    & Sheets(2).Cells(ActiveCell.Row, 4).Value & Chr(10) _
    & Sheets(2).Cells(ActiveCell.Row, 5).Value & Chr(10) _
    & Sheets(2).Cells(ActiveCell.Row, 6).Value & Chr(10) & Chr(10) _
    & "con:" & Chr(10) _
    & TextBox1.Value & Chr(10) _
    & TextBox2.Value & Chr(10) _
    & TextBox3.Value & Chr(10) _
    & TextBox4.Value & Chr(10) _
    & TextBox5.Value & Chr(10) _
    & TextBox6.Value

    Stile = vbYesNo + vbQuestion + vbDefaultButton2
    Risposta = MsgBox(Messaggio, Stile, Titolo)
    If Risposta = vbNo Then End
    Riga = ActiveCell.Row
    ComboBox1.Value = ""
    Sheets(2).Cells(Riga, 1) = TextBox1.Value
    Sheets(2).Cells(Riga, 2) = TextBox2.Value
    Sheets(2).Cells(Riga, 3) = TextBox3.Value
    Sheets(2).Cells(Riga, 4) = TextBox4.Value
    Sheets(2).Cells(Riga, 5) = TextBox5.Value
    Sheets(2).Cells(Riga, 6) = TextBox6.Value
    ComboBox1.SetFocus
End Sub

how can I do to indicate that with the insert new record function I find the last row on the database that I have moved to sheet 2? thank you

sheets(2).Cells(Range("A1").End(xlDown).Row + 1, 1).Select

it doesn't work and I go into error

why are you selecting the cells? You do nothing with them in the code snippet you show us. — Scott Craner, Jul 29 '21 at 18:51
Both lines in the code that end with `.Select` do not do anything. Remove them. — Scott Craner, Jul 29 '21 at 19:01
with that row, I select the first blank row after the last one to add new records — John22, Jul 29 '21 at 19:17
Do not use `.Select` and `ActiveCell` See: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba Instead code in a way that you do not need it. Either pass the information as variables to the subsequent subs or use public variables. Relying on `.Select` is fraught with issues. — Scott Craner, Jul 29 '21 at 19:20

score 0 · Answer 1 · edited Jul 29 '21 at 19:29

0

There are two easy methods. Try them out use what ever works fine for your case.

Method 1: Sheets(2).UsedRange.Rows.Count

Method 2: Sheets(2).Cells(Rows.Count, col_no).End(xlUp).Row

Here you need to change col_no to your desired column. For the above example it would be like

Method 2: Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row

These both method will give you last data containing row you need to add one ( +1 ) to get next blank row for new data entry.

edited Jul 29 '21 at 19:29

Dharman

30,962
25
85
135

answered Jul 29 '21 at 19:23

Mukibul Hasan

592
5
10

Try both instructions but it doesn't work – John22 Jul 29 '21 at 19:32
Make sure to clearly describe your problem & make it specific and small. These two methods are well tested & Works fine for me – Mukibul Hasan Jul 29 '21 at 20:11

how do i move this sub from sheet 1 to sheet 2

1 Answers1