Find the last hidden sheet

  • Thread starter Thread starter bramnizzle
  • Start date Start date
B

bramnizzle

I have created a workbook that has multiple hidden and unhidden
sheets. I have a macro that allows the user to select hidden sheets
from a list. However, another option I need is for the user to be
able to select the very last sheet whether it is hidden or not.

Worksheets(Worksheets.Count).Activate
only works for sheets that are not hidden.

I want a macro that finds the very last sheet in the workbook, whether
it is hidden or not.

I thought of using the SelectSheets macro I have, but again, I don't
want to create a list of all sheets, I just want it to go directly to
*the very last sheet*.

Can anyone help?

Thanks in advance.
 
[email protected] said:
I have created a workbook that has multiple hidden and unhidden
sheets. I have a macro that allows the user to select hidden sheets
from a list. However, another option I need is for the user to be
able to select the very last sheet whether it is hidden or not.

Worksheets(Worksheets.Count).Activate
only works for sheets that are not hidden.

I want a macro that finds the very last sheet in the workbook, whether
it is hidden or not.

I thought of using the SelectSheets macro I have, but again, I don't
want to create a list of all sheets, I just want it to go directly to
*the very last sheet*.

Can anyone help?

Thanks in advance.
Click to expand...
 
[email protected] said:
I have created a workbook that has multiple hidden and unhidden
sheets. I have a macro that allows the user to select hidden sheets
from a list. However, another option I need is for the user to be
able to select the very last sheet whether it is hidden or not.

Worksheets(Worksheets.Count).Activate
only works for sheets that are not hidden.

I want a macro that finds the very last sheet in the workbook, whether
it is hidden or not.

I thought of using the SelectSheets macro I have, but again, I don't
want to create a list of all sheets, I just want it to go directly to
*the very last sheet*.

Can anyone help?

Thanks in advance.
Click to expand...
 
Have sa button then runs the following:

Sub activateLastSheet()
Dim s As Worksheet

Set s = ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
If s.Visible = xlSheetHidden Or s.Visible = xlSheetVeryHidden Then
s.Visible = xlSheetVisible
s.Activate
End If
set s = Nothing
End Sub
 
Have sa button then runs the following:

Sub activateLastSheet()
Dim s As Worksheet

Set s = ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
If s.Visible = xlSheetHidden Or s.Visible = xlSheetVeryHidden Then
s.Visible = xlSheetVisible
s.Activate
End If
set s = Nothing
End Sub
Click to expand...

SWEET!!! Works like a charm. Thanks much!!!
 
Back
Top