Finding next empty row in another worksheet

Hello,

ok, this is probably a simply solution, that for whatever dumb reason is
elluding me.

I have a simple excel document. It's a workplace attendance tracking excel
document. Nothing complicated.

WS NAMES
WS1 = "input" (This is where the input for all agents information is entered)
WS2-8 = "agent name" (this is essentially a near copy of the input, but
only stores the data on that agent ).

WS1 has 8 simple buttons, and 6 checkbox's. Each checkbox corresponds to a row of input data (so max being copied to destination, is 6 rows of 9 columns of data).

Here's what I want to happen, input the data, and click the agent name.
The button command, will determine the appropriate destination worksheet (allready have this working), FIND THE NEXT BLANK ROW between ranges
A5 and A54, Then copy the files from checkmarked selected source (WS1), to the destination blank row.

The problem I am having is how to find the next blank ROW and set a variable with the row number.

here is the code I have so far :

'Total data range in INPUT sheet
Const DataSelectionA As String = "B6"
Const DataSelectionB As String = "J11"

'EACH row in data range in INPUT sheet
Const Option_1_RangeA As String = "B6"
Const Option_1_RangeB As String = "J6"
Const Option_2_RangeA As String = "B7"
Const Option_2_RangeB As String = "J7"
Const Option_3_RangeA As String = "B8"
Const Option_3_RangeB As String = "J8"
Const Option_4_RangeA As String = "B9"
Const Option_4_RangeB As String = "J9"
Const Option_5_RangeA As String = "B10"
Const Option_5_RangeB As String = "J10"
Const Option_6_RangeA As String = "B11"
Const Option_6_RangeB As String = "J11"

Public WhichAgent As String
Public WhichSheet As String
Public WhichRow As String

Public Sub Init()

Option1.Value = True
Option2.Value = False
Option3.Value = False
Option4.Value = False
Option5.Value = False
Option6.Value = False
Worksheets("INPUT").Activate
Worksheets("INPUT").Range(DataSelectionA, DataSelectionB).Value = ""
WhichAgent = "0"
WhichAgent = "nosheet"
WhichColumn = "A"
WhichRow = "5"
End Sub

Public Sub selectAgent()
If WhichAgent = 1 Then
 WhichSheet = "AGENT 1"
ElseIf WhichAgent = 2 Then
 WhichSheet = "AGENT 2"
ElseIf WhichAgent = 3 Then
 WhichSheet = "AGENT 3"
ElseIf WhichAgent = 4 Then
 WhichSheet = "AGENT 4"
ElseIf WhichAgent = 5 Then
 WhichSheet = "AGENT 5"
ElseIf WhichAgent = 6 Then
 WhichSheet = "AGENT 6"
ElseIf WhichAgent = 7 Then
 WhichSheet = "AGENT 7"
ElseIf WhichAgent = 8 Then
 WhichSheet = "AGENT 8"
ElseIf WhichAgent = 9 Then
 WhichSheet = "AGENT 9"
ElseIf WhichAgent = 10 Then
 WhichSheet = "AGENT 10"
Else
 WhichSheet = "nosheet"
End If

End Sub


Public Sub FindRow()
If WhichSheet <> "nosheet" Then

Worksheets(WhichSheet).Activate
' THIS IS WHERE I AM NOT SURE HOW TO FIND THE DESTINATION BLANK ROW.

Else
 MsgBox "Agent Unavailable!", vbOKOnly
 
 End If

End Sub


Public Sub MoveData()
'This is to be filled out afterwards, but I have a good idea of how to do this
End Sub

Private Sub reset_Click()
Call Init
End Sub

Private Sub Agent1_Click()
WhichAgent = "1"

Call selectAgent
Call FindRow
Call MoveData

End Sub

Private Sub Agent2_Click()
WhichAgent = "2"

Call selectAgent
Call FindRow
Call MoveData

End Sub

Private Sub Agent3_Click()
WhichAgent = "3"

Call selectAgent
Call FindRow
Call MoveData

End Sub

Private Sub Agent4_Click()
WhichAgent = "4"

Call selectAgent
Call FindRow
Call MoveData

End Sub

Private Sub Agent5_Click()
WhichAgent = "5"

Call selectAgent
Call FindRow
Call MoveData

End Sub

Private Sub Agent6_Click()
WhichAgent = "6"

Call selectAgent
Call FindRow
Call MoveData

End Sub

Private Sub Agent7_Click()
WhichAgent = "7"

Call selectAgent
Call FindRow
Call MoveData

End Sub

Private Sub Agent_8_Click()
WhichAgent = "8"

Call selectAgent
Call FindRow
Call MoveData

End Sub

Private Sub Agent_9_Click()
WhichAgent = "9"

Call selectAgent
Call FindRow
Call MoveData

End Sub

Private Sub Agent_10_Click()
WhichAgent = "10"

Call selectAgent
Call FindRow
Call MoveData

End Sub

Any help is apreciated,

Spyrule

Originally Posted by spyrule

Hello,

ok, this is probably a simply solution, that for whatever dumb reason is
elluding me.

The problem I am having is how to find the next blank ROW and set a variable with the row number.

Spyrule

something like

LastIRow = Sheets("Inspections").Range("A65536").End(xlUp).Row

---

Hey,

Wanted to say thank you, as that seems to have done the trick.

Here is the final code for that function :

LastIRow = Sheets(WhichSheet).Range("A65536").End(xlUp).Row

    If Option1.Value = True Then
        Worksheets("INPUT").Range("B6:J6").Copy Destination:=Worksheets(WhichSheet).Range("A" & LastIRow).End(xlToLeft).Offset(1, 0)
...

Thanks again,

Spyrule