search for sheet name

[andrew299]

is there a way of searching for a sheet of a specific name
like cells.find but sheets.find
I cant find one

thanks
andrew299

 

[SkipVought]

Hi,
No direct way, but...

MySheet = "Whatever"
Function FindSheet(MySheet as String) As Boolean
For Each Worksheet In Worksheets
   If Worksheet.Name = MySheet Then
      FindSheet = TRUE 
      Exit Function
   End IF
   FindSheet = FALSE
Next
End Function

Hope this helps Skip,
SkipAndMary1017@mindspring.com

 

[RobBroekhuis]

Although, if you already know the name, the following should also suffice:

MySheet="Whatever"
on error resume next
strDummy=sheets(MySheet).name
SheetFound=error<>0
on error goto 0

You would still need Skip's iterative approach if you're looking for a substring within a sheet's name, such as with the range.find method.
Rob

 

[SkipVought]

Wow! Here on tek-tips you get such wonderful synergy! Gotta luv it! Thanx, Rob!

By modifying my function you could search for a sub-string and then return the full worksheet name or &quot;&quot;. Skip,
SkipAndMary1017@mindspring.com

 

[xlbo]

Skip.......&quot;synergy&quot;...????
Have you come over all management consultant or something ???
LOL LOL LOL Rgds
Geoff

Si hoc legere scis, nimis eruditionis habes

 

[andrew299]

Ok thanks for all your help
one more thing though

If I have one sheet I know the name of &quot;main&quot; how do I delete all other sheets is ther a simple way?

thanks again

andrew299

 

[Nikita6003]

Hiya,

here's abit of code i use in one of my xl apps to deleta all save the settings sheet:

Public Function DeleteReportSheets() As Boolean
    Dim l_iCounter As Integer
    
    DeleteReportSheets = False
    On Error GoTo ErrorHandler
    
    'No screen messages
    Application.DisplayAlerts = False
    
    'Delete all sheets except SETTINGS
    For l_iCounter = 1 To g_udtSettings.wkbCurrent.Sheets.Count
        If (g_udtSettings.wkbCurrent.Sheets(g_udtSettings.wkbCurrent.Sheets.Count).Name <> &quot;Settings&quot;) Then
            g_udtSettings.wkbCurrent.Sheets(g_udtSettings.wkbCurrent.Sheets.Count).Delete
        End If
    Next l_iCounter
    
    'Turn on screen messages
    Application.DisplayAlerts = True
    
    DeleteReportSheets = True
    Exit Function
ErrorHandler:
    Call ErrorHandler(Err, &quot;DeleteReportSheets&quot;)
    Exit Function
    Resume Next
End Function

HTH ;-)

Cheers
Nikki

 

[andrew299]

Thanks Nikki that was kinda what I was using the trouble I was having was that with hundreds of sheets and doing operations similar to this several times the program takes several houirs to run. I was hoping there was a quick way of doing it. like selecting the main sheets and then inverting the selection but I cant find this function anywhere.

Thanks
Andrew299

 

[RobBroekhuis]

The easiest way would be to copy the main sheet to a new book. If you need to retain other features of your workbook, such as VBA code, you could try if the following works quicker:

Dim a(), i As Integer, j As Integer
ReDim a(Worksheets.Count - 2)
j = -1
For i = 1 To Worksheets.Count
If Worksheets(i).Name <> &quot;main&quot; Then
j = j + 1
a(j) = Worksheets(i).Name
End If
Next i
application.displayalerts=false
Sheets(a).Delete
application.displayalerts=true

This stores the worksheet names to be deleted in an array, and then deletes them all at once. Stands a chance of being a good bit quicker than deleting one at a time.
Rob

 

[andrew299]

It runs quicker now thanks
andrew299

 

[SkipVought]

Be sure that you have ScreenUpdating set to False Skip,
SkipAndMary1017@mindspring.com