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

 

[Legacy 143009]

No matter what the sheet names are, Sheets(Sheets.Count).Select should work. Have you tried to specify the workbook name also?

 

[picklefactory]

No matter what the sheet names are, Sheets(Sheets.Count).Select should work. Have you tried to specify the workbook name also?

Nope, frayed knot. I get a run time error and it hangs on that line. I've also tried

Workbooks("HOLIDAY BOOKING").Worksheets(Worksheets.Count).Select

But that also doesn't work

 

[MARK858]

What does the error state and the error number?

 

[Legacy 143009]

.Worksheet() method only accepts sheet names afaik. wb.Sheets(Sheets.Count) must work because Sheets() method can work with numbers. Let say you have 5 sheets. Sheets.Count will return 5. wb.Sheets(Sheets.Count).Select must select 5th sheet. It has nothing to do with names or sheet order. Very strange indeed...

 

[Fluff]

How about

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

 

[picklefactory]

What does the error state and the error number?

Hi Mark
On the Workbooks route I get 'Runtime error 9 Subscript out of range' and for the sheets method I get 'Runtime 1004 Select method of Worksheet class failed'

 

[picklefactory]

How about

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

Thanks Fluff, but that also gives the Runtime 9 error. I'll post the full code in case there's something else I'm doing that affects it

 

[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 Sub

 

[Fluff]

Try adding the file extension the workbook name.