Search if Sheet already exist in activeworkbook Overflow error

[Nikhil Damle]

I have written macro which loops through all rows in workbook and search if sheet already exist in another workbook. Here is the exact code

orderno = ThisWorkbook.Sheets("Sheet1").Range("V" & activerow).Value
Workbooks.Open (workdir & "Material Account\" & VendorName & ".xls")
Dim sh As Worksheet, iExist As Boolean
iExist = True
Set sh = Nothing
Set sh = Worksheets(orderno) 'Here is where err occur
If Err Then iExist = False
If iExist = True Then 'Sheet Exist

Else 'Sheet Does Not Exist

End if
ActiveWorkbook.Save
ActiveWorkbook.Close

This code generates overflow error(Run time error 6) and adding On Error Resume Next Does not search sheet. How to rectify this or is there any other way to search sheet in another workbook.
Thanks in Advance

 

[VoG]

What is activerow? Should it be ActiveCell.Row

 

[taurean]

Take a look at this function.
http://www.exceltip.com/st/Determin...orkbook_using_VBA_in_Microsoft_Excel/485.html

 

[Nikhil Damle]

thanks VoG and taurean for reply
Here activerow = ActiveCell.Row
taurean i checked the link and it worked for me
But if sheetname is not constant then it should be declared before using like

Dim mysheetname as string
activerow = ActiveCell.Row
orderno = ThisWorkbook.Sheets("Sheet1").Range("V" & activerow).Value
mysheetname = orderno
If Not SheetExists(MySheetName) Then
MsgBox(MySheetName & " doesn't exist!")
Else
Sheets(MySheetName).Activate
End If

but when mysheetname is directly used(see below code) compile error ByRef argument type mismatch occur.

activerow = ActiveCell.Row
orderno = ThisWorkbook.Sheets("Sheet1").Range("V" & activerow).Value
mysheetname = orderno
If Not SheetExists(MySheetName) Then
MsgBox(MySheetName & " doesn't exist!")
Else
Sheets(MySheetName).Activate
End If

 

[taurean]

I think, you need to be doubly sure that you have selected correct Sheet i.e. Sheet1 while running the code. See if the below code with added checks works for you:

Public Sub CheckSheet()
Dim MySheetName As String
Dim lActiveRow As Long
'Checking if we are on the right sheet i.e. Sheet1
If ActiveSheet.Name <> "Sheet1" Then MsgBox "Not on the correct sheet!": Exit Sub
lActiveRow = ActiveCell.Row
MySheetName = ThisWorkbook.Sheets("Sheet1").Range("V" & lActiveRow).Value
'Checking if at least non-blank info is provided on the active row.
If MySheetName = "" Then MsgBox "There's no Sheet Name Specified in Column V!": Exit Sub
If Not SheetExists(MySheetName) Then
MsgBox (MySheetName & " doesn't exist!")
Else
Sheets(MySheetName).Activate
End If
End Sub