The last LastRow problem

[NotMe2]

Hi there.
Looking for help with vb script that inserts data to 3 different worksheets using an userform with a commandbutton_click.
Found a script (non AI) LastRow that works for the first worksheet (Guests), but for the other sheets Diver Details and Book and Travel it will put the entered data not on the same row if the first column of these sheets is empty.
Have tried different solutions, Dim i As Long, Dim LastRow1 As Long andRange("A" & LastRow + 1) etc., but the empty cells in previous rows still got filled out or were overwritten.
There are so many lastrow questions, but none addressed mine.
Here is the code

Sub CommandButton1_Click()

'Declare variables
Dim wsGuests As Worksheet
Dim wsDiverdetails As Worksheet
Dim wsBookTravel As Worksheet
Dim LastRow As Long

'Set worksheets
Set wsGuests = ThisWorkbook.Sheets("Guests")
Set wsDiverdetails = ThisWorkbook.Sheets("Diver details")
Set wsBookTravel = ThisWorkbook.Sheets("Book and Travel")

LastRow = Guests.Cells(ws1.Rows.Count, "A").End(xlUp).Row
wsGuests.Cells(LastRow + 1, 1).Value = Me.tbFirst_name.Value
wsGuests.Cells(LastRow + 1, 2).Value = Me.tbLast_name.Value
etc.
LastRow = wsDiverdetails.Cells(ws2.Rows.Count, "A").End(xlUp).Row
wsDiverdetails.Cells(LastRow + 1, 1).Value = Me.tbCert_level.Value
wsDiverdetails.Cells(LastRow + 1, 2).Value = Me.tbNod.Value
etc.
LastRow = wsBookTravel.Cells(ws2.Rows.Count, "A").End(xlUp).Row
wsBookTravel.Cells(LastRow + 1, 1).Value = Me.tbDep_date.Value
wsBookTravel.Cells(LastRow + 1, 2).Value = Me.tbLeave_date.Value
etc.
End Sub

Hope that there is someone who can help me with this dilemma and that I have provided enough info to solve this.

 

[DanteAmor]

Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

You are referencing sheet ws1 and that object does not exist.
You must reference the sheet or just don't reference the sheet, since all sheets have the same number of rows, it is not necessary in this case to reference the sheet.

In the first line you have a reference to the Guest object and it should be wsGuests.

  LastRow = Guests.Cells(ws1.Rows.Count, "A").End(xlUp).Row

  'etc.
 
  LastRow = wsDiverdetails.Cells(ws2.Rows.Count, "A").End(xlUp).Row

  'etc.
 
  LastRow = wsBookTravel.Cells(ws2.Rows.Count, "A").End(xlUp).Row

I'm using: LastRow = wsGuests.Cells(Rows.Count, "A").End(xlUp).Row + 1 and I add a one to it, that way you only do the addition once.
Consider that column A in each sheet must have data otherwise the last row with data in column A will not be the last row with data in the last row in the sheet.
To ensure that I put some validations in the textboxes that store information in column A so that you always put data in it.
Try this:

Sub CommandButton1_Click()

  'Declare variables
  Dim wsGuests As Worksheet
  Dim wsDiverdetails As Worksheet
  Dim wsBookTravel As Worksheet
  Dim LastRow As Long
 
  'Set worksheets
  Set wsGuests = ThisWorkbook.Sheets("Guests")
  Set wsDiverdetails = ThisWorkbook.Sheets("Diver details")
  Set wsBookTravel = ThisWorkbook.Sheets("Book and Travel")
 
  'Validations:
  If Me.tbFirst_name.Value = "" Then
    MsgBox "Enter data First Name"
    Me.tbFirst_name.SetFocus
    Exit Sub
  End If
  If Me.tbCert_level.Value = "" Then
    MsgBox "Enter data Level"
    Me.tbCert_level.SetFocus
    Exit Sub
  End If
  If Me.tbDep_date.Value = "" Then
    MsgBox "Enter data Dep"
    Me.tbDep_date.SetFocus
    Exit Sub
  End If
 
  LastRow = wsGuests.Cells(Rows.Count, "A").End(xlUp).Row + 1
  wsGuests.Cells(LastRow, 1).Value = Me.tbFirst_name.Value
  wsGuests.Cells(LastRow, 2).Value = Me.tbLast_name.Value
  'etc.
 
  LastRow = wsDiverdetails.Cells(Rows.Count, "A").End(xlUp).Row + 1
  wsDiverdetails.Cells(LastRow, 1).Value = Me.tbCert_level.Value
  wsDiverdetails.Cells(LastRow, 2).Value = Me.tbNod.Value
  'etc.
 
  LastRow = wsBookTravel.Cells(Rows.Count, "A").End(xlUp).Row + 1
  wsBookTravel.Cells(LastRow, 1).Value = Me.tbDep_date.Value
  wsBookTravel.Cells(LastRow, 2).Value = Me.tbLeave_date.Value
  'etc.
End Sub

-------------------
If the row where you are going to write the data must be the same in the 3 sheets, then you only have to obtain the last row with data from the first sheet and use that row in the 3 sheets:

Sub CommandButton1_Click()

  'Declare variables
  Dim wsGuests As Worksheet
  Dim wsDiverdetails As Worksheet
  Dim wsBookTravel As Worksheet
  Dim LastRow As Long
  
  'Set worksheets
  Set wsGuests = ThisWorkbook.Sheets("Guests")
  Set wsDiverdetails = ThisWorkbook.Sheets("Diver details")
  Set wsBookTravel = ThisWorkbook.Sheets("Book and Travel")
  
  'Validations:
  If Me.tbFirst_name.Value = "" Then
    MsgBox "Enter data First Name"
    Me.tbFirst_name.SetFocus
    Exit Sub
  End If
  
  LastRow = wsGuests.Cells(Rows.Count, "A").End(xlUp).Row + 1       'same row on all 3 sheets
  wsGuests.Cells(LastRow, 1).Value = Me.tbFirst_name.Value
  wsGuests.Cells(LastRow, 2).Value = Me.tbLast_name.Value
  'etc.
  
  wsDiverdetails.Cells(LastRow, 1).Value = Me.tbCert_level.Value
  wsDiverdetails.Cells(LastRow, 2).Value = Me.tbNod.Value
  'etc.
  
  wsBookTravel.Cells(LastRow, 1).Value = Me.tbDep_date.Value
  wsBookTravel.Cells(LastRow, 2).Value = Me.tbLeave_date.Value
  'etc.
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[Skyybot]

It's good practice to completely define your Sheets, as well, just in case you have other open Workbooks (like PERSONAL MACROS or Templates) that open with Excel.

Dim wb as Workbooks
Dim sht as Worksheet
Set wb = Workbooks("WorkbookName.xls"): Set sht = wb.Sheets("Sheet1")

 

[dmt32]

It's good practice to completely define your Sheets, as well, just in case you have other open Workbooks (like PERSONAL MACROS or Templates) that open with Excel.

OP has qualified their worksheets with the ThisWorkbook Property which Returns the Workbook object where the current macro code is running.

Set wsGuests = ThisWorkbook.Sheets("Guests")

Also, the Sheets collection contains all the sheets in the workbook (both chart sheets and worksheets).

When working with sheets of a known type, good practice to specify the correct object type

Set sht = wb.Worksheets("Sheet1")

Dave

 

[NotMe2]

Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

You are referencing sheet ws1 and that object does not exist.
You must reference the sheet or just don't reference the sheet, since all sheets have the same number of rows, it is not necessary in this case to reference the sheet.

In the first line you have a reference to the Guest object and it should be wsGuests.

  LastRow = Guests.Cells(ws1.Rows.Count, "A").End(xlUp).Row

  'etc.
 
  LastRow = wsDiverdetails.Cells(ws2.Rows.Count, "A").End(xlUp).Row

  'etc.
 
  LastRow = wsBookTravel.Cells(ws2.Rows.Count, "A").End(xlUp).Row

I'm using: LastRow = wsGuests.Cells(Rows.Count, "A").End(xlUp).Row + 1 and I add a one to it, that way you only do the addition once.
Consider that column A in each sheet must have data otherwise the last row with data in column A will not be the last row with data in the last row in the sheet.
To ensure that I put some validations in the textboxes that store information in column A so that you always put data in it.
Try this:

Sub CommandButton1_Click()

  'Declare variables
  Dim wsGuests As Worksheet
  Dim wsDiverdetails As Worksheet
  Dim wsBookTravel As Worksheet
  Dim LastRow As Long
 
  'Set worksheets
  Set wsGuests = ThisWorkbook.Sheets("Guests")
  Set wsDiverdetails = ThisWorkbook.Sheets("Diver details")
  Set wsBookTravel = ThisWorkbook.Sheets("Book and Travel")
 
  'Validations:
  If Me.tbFirst_name.Value = "" Then
    MsgBox "Enter data First Name"
    Me.tbFirst_name.SetFocus
    Exit Sub
  End If
  If Me.tbCert_level.Value = "" Then
    MsgBox "Enter data Level"
    Me.tbCert_level.SetFocus
    Exit Sub
  End If
  If Me.tbDep_date.Value = "" Then
    MsgBox "Enter data Dep"
    Me.tbDep_date.SetFocus
    Exit Sub
  End If
 
  LastRow = wsGuests.Cells(Rows.Count, "A").End(xlUp).Row + 1
  wsGuests.Cells(LastRow, 1).Value = Me.tbFirst_name.Value
  wsGuests.Cells(LastRow, 2).Value = Me.tbLast_name.Value
  'etc.
 
  LastRow = wsDiverdetails.Cells(Rows.Count, "A").End(xlUp).Row + 1
  wsDiverdetails.Cells(LastRow, 1).Value = Me.tbCert_level.Value
  wsDiverdetails.Cells(LastRow, 2).Value = Me.tbNod.Value
  'etc.
 
  LastRow = wsBookTravel.Cells(Rows.Count, "A").End(xlUp).Row + 1
  wsBookTravel.Cells(LastRow, 1).Value = Me.tbDep_date.Value
  wsBookTravel.Cells(LastRow, 2).Value = Me.tbLeave_date.Value
  'etc.
End Sub

-------------------
If the row where you are going to write the data must be the same in the 3 sheets, then you only have to obtain the last row with data from the first sheet and use that row in the 3 sheets:

Sub CommandButton1_Click()

  'Declare variables
  Dim wsGuests As Worksheet
  Dim wsDiverdetails As Worksheet
  Dim wsBookTravel As Worksheet
  Dim LastRow As Long
 
  'Set worksheets
  Set wsGuests = ThisWorkbook.Sheets("Guests")
  Set wsDiverdetails = ThisWorkbook.Sheets("Diver details")
  Set wsBookTravel = ThisWorkbook.Sheets("Book and Travel")
 
  'Validations:
  If Me.tbFirst_name.Value = "" Then
    MsgBox "Enter data First Name"
    Me.tbFirst_name.SetFocus
    Exit Sub
  End If
 
  LastRow = wsGuests.Cells(Rows.Count, "A").End(xlUp).Row + 1       'same row on all 3 sheets
  wsGuests.Cells(LastRow, 1).Value = Me.tbFirst_name.Value
  wsGuests.Cells(LastRow, 2).Value = Me.tbLast_name.Value
  'etc.
 
  wsDiverdetails.Cells(LastRow, 1).Value = Me.tbCert_level.Value
  wsDiverdetails.Cells(LastRow, 2).Value = Me.tbNod.Value
  'etc.
 
  wsBookTravel.Cells(LastRow, 1).Value = Me.tbDep_date.Value
  wsBookTravel.Cells(LastRow, 2).Value = Me.tbLeave_date.Value
  'etc.
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

@Dante, thank you so much for your help, it works now.
Since you mentioned Column A always has to have a value, I was thinking off having an unique ID in Column A in all 3 sheets, so that the message alert would be redundant.
Is there a way to incooperate this in the above code e.g. GU-1 as unique ID number