PDA

View Full Version : [SOLVED] Get sheets name



ilyaskazi
05-20-2005, 11:38 PM
following is the code given which detects activesheet.name and display msgbox for each-n-every sheets...



Sub getSht_Name()
cnShts = Application.Sheets.Count
For i = 1 To cnShts
Sheets(i).Select
shtname = ActiveSheet.Name
MsgBox shtname
Next i
End Sub


i m attaching 1 file here for which this code does'nt work.
I hv checked this with some other normal excel files and got the output correct. But unable to trace out with this file attached here.
Plz somebody check and let me know.

Jacob Hilderbrand
05-20-2005, 11:46 PM
You cannot select a hidden sheet so your code fails because there is a hidden sheet in this workbook. But you do not even need to select anything.



shtname = Sheets(i).Name

ilyaskazi
05-21-2005, 12:40 AM
ok.

so now i need them to delete all the hidden sheets only if found..

how to detect only hidden sheets and delete through vba

Bob Phillips
05-21-2005, 01:09 AM
ok.

so now i need them to delete all the hidden sheets only if found..

how to detect only hidden sheets and delete through vba



This delete hidden and very hidden sheets



Sub DeleteHiddenSheets()
Dim sh As Worksheet
Application.DisplayAlerts = False
For Each sh In ThisWorkbook.Worksheets
If sh.Visible <> xlSheetVisible Then
sh.Visible = True
sh.Delete
End If
Next sh
Application.DisplayAlerts = True
End Sub

ilyaskazi
05-21-2005, 03:44 AM
thanking you all,

it is solved and perfect.