Phil


Unless you are using the Option Explicit option for your module sheet
when you run a macro Excel will automatically create any undeclare
variables at the time the macro or function is run.
What you have is a variable within Sub ImportFiles() called Main_wb
and anther variable within Function Find_Sheet(s_name As String) A
Integer also called Main_wb.

Main_wb in Function Find_Sheet(s_name As String) As Integer has n
instructions as to what it refers to.

you need to declare the variable at the top of the module sheet

Dim Main_wb as Worksheets
or
Private Main_wb as Worksheets
or
Public Main_wb as Worksheets

Sub ImportFiles()

' Record the main workbook
Set Main_wb = ActiveWorkbook

'more code blah blah

ws_index = Find_Sheet(Sheet_Name)
End Sub

Function Find_Sheet(s_name As String) As Integer
' Will check the workbook for a sheet for this name;
' If found returns the worksheets index, if not found inserts it an
returns index
Dim ws_index As Integer

' Cycle through the sheets
With Main_wb
MsgBox .Worksheets.Count ' <==== HERE
For ws_index = 0 To .Worksheets.Count
If .Worksheets(ws_index).name = s_name Then
Find_Sheet = ws_index
Exit Function
End If
Next
.Worksheets.Add Befo=.Worksheets(1)
.Worksheets(1).name = s_name
End With
End Functio

--
mudrake
-----------------------------------------------------------------------
mudraker's Profile: http://www.excelforum.com/member.php...nfo&userid=247
View this thread: http://www.excelforum.com/showthread.php?threadid=52337