find sheet

[andrew299]

I have a macro that creates an increasing number of sheets as it runs. At each step it searches through these sheets to see if a certain one has been created in the workbook. Several workbooks are used in this macro and the code cycles though depending on the workbook. I want to speed up this process. The idea I have had is like this


On error goto notfound

with wkbtemp '=open workbook
found=true
worksheets(sheetfind).activate 'sheetfind=worksheet name
end with



notfound:
found=false
resume next


It should try to open the sheet if it is there then found = true if not it goes to error handler notfound and found=false.
The problem is that when I run it and it gets to the activate line i get an error (error handler disabled until it is running properly) even when I know the sheet exists.
Run time error 9
Subscript out of range.
I cant see what the problem is.
Any help appreciated

Andrew

It may have hit every branch on its way out of the ugly tree, but hey! It works. (but don't quote me on that)

 

[andrew299]

Problem solved!!
It wasnt activating the workbook I wanted to. I put in the line
wkbtemp.activate
between the with and found line and now it works fine.
Andrew

It may have hit every branch on its way out of the ugly tree, but hey! It works. (but don't quote me on that)

 

[PHV]

Something like this ?
Dim s As Worksheet
On Error Resume Next
With wkbtemp
Set s = .Worksheets(sheetfind)
found = Not s Is Nothing
End With



Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[andrew299]

Thanks PHV
With these small changes I have managed to cut the macro time from 12 minutes to 0.025 Seconds (Or so my timer tells me)
Andrew

It may have hit every branch on its way out of the ugly tree, but hey! It works. (but don't quote me on that)