Select last sheet in wb when sheets are named

[picklefactory]

Hi folks
Trying to find a way to simply select/activate the last sheet in a workbook (Last sheet extreme rh side of tabs, not last sheet used)
Sheets(Sheets.Count).Select doesn't seem to work, being as I am dynamically renaming each sheet as it is created, so I do not have Sheet1, Sheet2 etc.
Is there another way?
Thanks

 

[MARK858]

'Runtime error 9 Subscript out of range'

Is normally a naming error, check the number of spaces (including leading and trailing spaces in the workbook name (and try with the file extension as @Fluff has suggested)

'Runtime 1004 Select method of Worksheet class failed'

Do you have any workbook or worksheet protection active?

This line would probably mean yes

MsgBox "WRONG PASSWORD.... BUGGER OFF!!", vbCritical + vbOKOnly, "Access denied"

 

[picklefactory]

The full code is below, I'm using a UserForm as a password entry and depending on the password acceptance, there are further function.

Private Sub CommandButton1_Click()
If TextBox1.Value = "Name" Then

Worksheets("Employee List").Range("J10").Value = "Name"

Worksheets("Input").Activate

ActiveWindow.DisplayWorkbookTabs = True

Unload Me

UserFormMain.Hide

With Workbooks("HOLIDAY BOOKING")
   .Activate
   .Worksheets(.Worksheets.Count).Select
End With

Else
MsgBox "WRONG PASSWORD.... BUGGER OFF!!", vbCritical + vbOKOnly, "Access denied"
Unload Me



End If

End Su
[/QUOTE]

Try adding the file extension the workbook name.

That gets past the previous error, but then just hangs on the Worksheets line with the runtime 1004 error

 

[picklefactory]

Is normally a naming error, check the number of spaces (including leading and trailing spaces in the workbook name (and try with the file extension as @Fluff has suggested)


Do you have any workbook or worksheet protection active?

This line would probably mean yes

MsgBox "WRONG PASSWORD.... BUGGER OFF!!", vbCritical + vbOKOnly, "Access denied"

"Do you have any workbook or worksheet protection active?"
Yes, I do..... I'll have a play with that

 

[picklefactory]

Bugger! Sussed it, I think. Your question on protection made me twig. I forgot that I have 2 sheets hidden!!! Doh! I just unhid them and it works fine. I'll add a line to unhide, select last sheet then hide again. Hopefully that should sort it.
Most of the problems I have are self-inflicted daftness

 

[picklefactory]

Yep, sorted. My own stupid fault, as usual.

Private Sub CommandButton1_Click()
If TextBox1.Value = "name" Then

Worksheets("Employee List").Range("J10").Value = "Name"

Worksheets("Input").Activate

ActiveWindow.DisplayWorkbookTabs = True

Unload Me

UserFormMain.Hide

Dim ws As Worksheet
   
    For Each ws In ActiveWorkbook.Worksheets
       
        ws.Visible = xlSheetVisible
   
    Next ws

Sheets(Sheets.Count).Select

Else
MsgBox "WRONG PASSWORD.... BUGGER OFF!!", vbCritical + vbOKOnly, "Access denied"
Unload Me



End If

End Sub

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[picklefactory]

Thank you all for your time, you lot usually either solve my issues instantly or push me into the right place to spot my own dumbass errors. To be fair, most of my VBA issues are pretty basic

 

[Alex Blakenburg]

If you didn't want to unhide them you could try something like this.

Sub GetLastVisibleSheet()

    Dim iSht As Long
    Dim lastSht As Worksheet
   
    With Workbooks("HOLIDAY BOOKING.xlsx")              ' Change extension to correct extension
       .Activate
      
       Set lastSht = .Worksheets(.Worksheets.Count)
       If lastSht.Visible = False Then
            For iSht = (.Worksheets.Count - 1) To 1 Step -1
                If .Worksheets(iSht).Visible = True Then
                    Set lastSht = .Worksheets(iSht)
                    Exit For
                End If
            Next iSht
        End If
        lastSht.Select
    End With

End Sub

 

[picklefactory]

If you didn't want to unhide them you could try something like this.

Sub GetLastVisibleSheet()

    Dim iSht As Long
    Dim lastSht As Worksheet
  
    With Workbooks("HOLIDAY BOOKING.xlsx")              ' Change extension to correct extension
       .Activate
     
       Set lastSht = .Worksheets(.Worksheets.Count)
       If lastSht.Visible = False Then
            For iSht = (.Worksheets.Count - 1) To 1 Step -1
                If .Worksheets(iSht).Visible = True Then
                    Set lastSht = .Worksheets(iSht)
                    Exit For
                End If
            Next iSht
        End If
        lastSht.Select
    End With

End Sub

Ooh, that's pretty...... and it works too. Thank you

 

[Alex Blakenburg]

Thanks for the confirmation. It is always nice to have options