CODEWITHSUNDEEP
excelvbacells
Driver8
Driver8

Reputation: 43

Select first empty cell in specific column when switching between worksheets

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.

Upvotes: 1

Views: 4335

Answers (3)

DisplayName
DisplayName

Reputation: 13386

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

Upvotes: 1

Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 11978

With this code, typing a sheet name in cell A1 from sheet1 will activate the sheet you type, and the active cell will be the first one in blank in column A.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("A1"), Target) Is Nothing Then Exit Sub
Sheets(Target.Value).Activate
ActiveSheet.Cells(Sheets(Target.Value).Range("A1").End(xlDown).Row + 1, 1).Select
End Sub

Upvotes: 0

shash
shash

Reputation: 266

This should get you to the first empty cell in Column A

rowEmpty = Range("A" & 10000).End(xlup).Row +1

You can replace the number 10000 with a sufficiently large number that ensures there will be no data there. There are alternatives to that, but I think this one is easier to begin with.

Upvotes: 1

Related Questions