Use this function


'-----------------------------------------------------------------
Function SheetExists(Sh As String, _
Optional wb As Workbook) As Boolean
'-----------------------------------------------------------------
Dim oWs As Worksheet
If wb Is Nothing Then Set wb = ActiveWorkbook
On Error Resume Next
SheetExists = CBool(Not wb.Worksheets(Sh) Is Nothing)
On Error GoTo 0
End Function


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ctech" wrote in
message ...

My macro opens all workbooks in a specified folder and copies a range
from a certain sheet. However I have now a problem as not all of the
workbooks contains worksheet "Sch 7A".

How can I add an error handler which so something like this..

If sheet doen't exsisit, then goto next workbook.

My macro:

Sub GetCellsFromWorkbooks()
'
' Macro1 Macro
' Macro recorded 31/10/2005 by Taylor Nelson Sofres plc
'

'

Dim Mnumb
Dim Aworkbook
Dim Aworkbook2
Dim AWorkbook3

AWorkbook3 = Application.ActiveWorkbook.Name
Mnumb = 101
Range("A8").Select

' On Error GoTo Errorhandler

For i = 1 To 850

Application.Workbooks.Open Filename:= _
"X:\Users\Shared\GENERAL\Christian S\05.10.28 - Budget packs -
Capital expenditure - comments\Test\BFR " & Mnumb & " bud v2.1.xls" _
, UpdateLinks:=0


Aworkbook = Workbooks("BFR " & Mnumb & " bud v2.1.xls").Name

' Taken out the save without password bit

'Application.DisplayAlerts = False
'
' ActiveWorkbook.SaveAs FileName:= _
' "X:\Users\Shared\GENERAL\Christian S\05.10.28 - Budget packs -
Capital expenditure - comments\" & Aworkbook _
' , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
' ReadOnlyRecommended:=False, CreateBackup:=False

' Set cost center name


Workbooks.Add.Activate

ActiveWorkbook.SaveAs Filename:= _
"X:\Users\Shared\GENERAL\Christian S\05.10.28 - Budget packs -
Capital expenditure - comments\Test\BFR " & Mnumb & " bud v2.1-2.xls"
_
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

Aworkbook2 = Workbooks("BFR " & Mnumb & " bud v2.1-2.xls").Name



ActiveCell = Mnumb



' All sheets

Dim Morg
Dim Mto

Morg = Lbud.TextBox_org
Mto = Lbud.TextBox_to

Dim Sht As Worksheet

On Error Resume Next

For Each Sht In Worksheets
Application.Workbooks(Aworkbook).Sheets("Sch
7A").Range("A1:X250").Select
Selection.Copy

Application.Workbooks(Aworkbook2).Select
Application.Workbooks(Aworkbook2).Sheets.Add
ActiveSheet.Range("A1").Select
ActiveSheet.Paste

Next

On Error GoTo 0




' Select cell for next i + 1

Application.CutCopyMode = False

' ActiveWorkbook.SaveAs Filename:= _
"X:\Users\Shared\GENERAL\Christian S\05.10.28 - Budget packs -
Capital expenditure - comments\Test\BFR " & Mnumb & " bud v2.1-2.xls"
_
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


Application.Workbooks("BFR " & Mnumb & " bud v2.1.xls").Close

Application.CutCopyMode = False


Mnumb = Mnumb + 1
Next i

Errorhandler:

Mnumb = Mnumb + 1

Resume


End Sub


--
Ctech
------------------------------------------------------------------------
Ctech's Profile:
http://www.excelforum.com/member.php...o&userid=27745
View this thread: http://www.excelforum.com/showthread...hreadid=483865