Select first empty cell in specific column when switching between worksheets

Question

I have a single workbook with multiple sheets. Sheet 1 uses cell A1 as a search box to find a specific sheet in the book. The sheets after Sheet 1 are named like this, SO123456 with different numbers following the SO. I have the following code on sheet 1 to open the corresponding sheet from the value typed into A1 of Sheet 1:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("A1"), Target) Is Nothing Then Exit Sub
Sheets(Target.Value).Activate
End Sub

When the corresponding sheet is opened, I want to open it to the next empty cell in column A. I have some code on the SOxxxxxx sheets that populates time in Column C when my data is entered in Column A. Here is that code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Columns("A:A")) Is Nothing Then
         Target.Offset(0, 2).Value = Now
End If

End Sub

I'm not sure how to make sure the first empty cell in Column A is selected or where the code goes; sheet being opened or where the call is to open the sheet. I tried entering the following code below the Worksheet_Change block with no luck:

Range("A" & Rows.Count).End(xlup).Offset(1).Row
Cells(Rows.Count,1).End(xlup).Offset(1).Row
UsedRange.Rows.Count

I know next to nothing about VBA if you haven't already noticed.

Answer 1

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address <> "A$1” Then Exit Sub
    With WorkSheets(Target.Value)
         .Activate
         .Cells(.Rows.Count,1).End(xlup).Offset(1).Select
    End With
End Sub