How to check worksheets exist or not ?

Dim loWS As Worksheet
Set loWS = Application.Workbooks("myWorkBook").Worksheets("myWorksheet")

Trap the error that will be raised if "myWorksheet" does not exist. e.g.

Dim loWS As Worksheet

On Error Resume Next
Set loWS = Application.Workbooks("myWorkBook").Worksheets("myWorksheet")
'return to normal error handling
On Error GoTo 0

If loWS is Nothing Then MsgBox "Workbooks > myWorkBook > Worksheets >
myWorksheet does not exist."

Note that this maybe because Workbooks("myWorkBook") is not open also

NickHK

"moonhk" <moon_ils-(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> How to check worksheets exist or not ?
>
> Dim loWS As Worksheet
> Set loWS = Application.Workbooks("myWorkBook").Worksheets("myWorksheet")
>

Hi


....
Dim varSheet As String
Dim varCont As Boolean
....
varSheet="myWorksheet"

For Each ws In Worksheets
varCont = IIf(varCont, True, ws.Name = varTargetSheet)
Next ws
If varCont = False Then
' Your action(s) when searched sheet was missing
End If
....


--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )



"moonhk" <moon_ils-(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> How to check worksheets exist or not ?
>
> Dim loWS As Worksheet
> Set loWS = Application.Workbooks("myWorkBook").Worksheets("myWorksheet")
>

On Error Resume Next
SheetExists = CBool(Not Application.Workbooks("myWorkBook") _
.Worksheets("myWorksheet") Is Nothing)
On Error GoTo 0
If SheetExists Then
Set loWS =
Application.Workbooks("myWorkBook").Worksheets("myWorksheet")


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"moonhk" <moon_ils-(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> How to check worksheets exist or not ?
>
> Dim loWS As Worksheet
> Set loWS = Application.Workbooks("myWorkBook").Worksheets("myWorksheet")
>

Thank, I am using below coding

Public Sub Build_CIM()
On Error Resume Next
Dim loBook As Workbook
Dim loSheet As Worksheet
Dim loBookName As String
Dim loSheetName As String
Dim loCIM As Worksheet
Dim cnt, cimCnt As Long
cim.init
loBookName = Application.ActiveWorkbook.Name
loSheetName = Application.ActiveSheet.Name
Application.StatusBar = "Processing..." & loSheetName
MsgBox loSheetName
Set loCIM =
Application.Workbooks("apvomt_v5.xls").Worksheets("Detail")
If loCIM Is Nothing Then
MsgBox "Workbook not opened apvomt_v5.xls"
Exit Sub
End If
Set loSheet =
Application.Workbooks(loBookName).Worksheets(loSheetName)
'~~ Temp
If loSheet Is Nothing Then
MsgBox "Workbook not opened " & loBookName & " " & loSheetName
Exit Sub
End If
option
site.getConfig ("SH-451455")
cnt = 3
cimCnt = 7
'~~MsgBox site.s1dnAddr & "CIM=" & cim.s2BatchAddr
Do
'~~ Setup Invoice Value
loCIM.Range(cim.s2InvoiceAddr & cimCnt).Value = _
loSheet.Range(site.s1dnAddr & cnt).Value
'~~ Update counter
cimCnt = cimCnt + 1
cnt = cnt + 1
Loop While VBA.Trim(loSheet.Range(site.s1dnAddr & cnt)) <> ""
MsgBox cnt
End Sub


Bob Phillips wrote:
> On Error Resume Next
> SheetExists = CBool(Not Application.Workbooks("myWorkBook") _
> .Worksheets("myWorksheet") Is Nothing)
> On Error GoTo 0
> If SheetExists Then
> Set loWS =
> Application.Workbooks("myWorkBook").Worksheets("myWorksheet")
>
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "moonhk" <moon_ils-(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > How to check worksheets exist or not ?
> >
> > Dim loWS As Worksheet
> > Set loWS = Application.Workbooks("myWorkBook").Worksheets("myWorksheet")
> >

Unless you have good reason, it is not advisable to run the whole routine
with "On Error Resume Next" active.

Make it active when you expect an error, deal with it and return to 'normal'
error handling. As in Bob's example:

On Error Resume Next
Set loCIM =Application.Workbooks("apvomt_v5.xls").Worksheets("Detail")
On Error GoTo 0
If loCIM Is Nothing Then

NickHK

"moonhk" <moon_ils-(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Thank, I am using below coding
>
> Public Sub Build_CIM()
> On Error Resume Next
> Dim loBook As Workbook
> Dim loSheet As Worksheet
> Dim loBookName As String
> Dim loSheetName As String
> Dim loCIM As Worksheet
> Dim cnt, cimCnt As Long
> cim.init
> loBookName = Application.ActiveWorkbook.Name
> loSheetName = Application.ActiveSheet.Name
> Application.StatusBar = "Processing..." & loSheetName
> MsgBox loSheetName
> Set loCIM =
> Application.Workbooks("apvomt_v5.xls").Worksheets("Detail")
> If loCIM Is Nothing Then
> MsgBox "Workbook not opened apvomt_v5.xls"
> Exit Sub
> End If
> Set loSheet =
> Application.Workbooks(loBookName).Worksheets(loSheetName)
> '~~ Temp
> If loSheet Is Nothing Then
> MsgBox "Workbook not opened " & loBookName & " " & loSheetName
> Exit Sub
> End If
> option
> site.getConfig ("SH-451455")
> cnt = 3
> cimCnt = 7
> '~~MsgBox site.s1dnAddr & "CIM=" & cim.s2BatchAddr
> Do
> '~~ Setup Invoice Value
> loCIM.Range(cim.s2InvoiceAddr & cimCnt).Value = _
> loSheet.Range(site.s1dnAddr & cnt).Value
> '~~ Update counter
> cimCnt = cimCnt + 1
> cnt = cnt + 1
> Loop While VBA.Trim(loSheet.Range(site.s1dnAddr & cnt)) <> ""
> MsgBox cnt
> End Sub
>
>
> Bob Phillips wrote:
> > On Error Resume Next
> > SheetExists = CBool(Not Application.Workbooks("myWorkBook") _
> > .Worksheets("myWorksheet") Is Nothing)
> > On Error GoTo 0
> > If SheetExists Then
> > Set loWS =
> > Application.Workbooks("myWorkBook").Worksheets("myWorksheet")
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > "moonhk" <moon_ils-(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > How to check worksheets exist or not ?
> > >
> > > Dim loWS As Worksheet
> > > Set loWS =
Application.Workbooks("myWorkBook").Worksheets("myWorksheet")
> > >
>

Hi NickHK
I just testing below coding. Very good result
On Error GoTo 0

Thank a lot
Moonhk


NickHK wrote:
> Unless you have good reason, it is not advisable to run the whole routine
> with "On Error Resume Next" active.
>
> Make it active when you expect an error, deal with it and return to 'normal'
> error handling. As in Bob's example:
>
> On Error Resume Next
> Set loCIM =Application.Workbooks("apvomt_v5.xls").Worksheets("Detail")
> On Error GoTo 0
> If loCIM Is Nothing Then
>
> NickHK
>
> "moonhk" <moon_ils-(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Thank, I am using below coding
> >
> > Public Sub Build_CIM()
> > On Error Resume Next
> > Dim loBook As Workbook
> > Dim loSheet As Worksheet
> > Dim loBookName As String
> > Dim loSheetName As String
> > Dim loCIM As Worksheet
> > Dim cnt, cimCnt As Long
> > cim.init
> > loBookName = Application.ActiveWorkbook.Name
> > loSheetName = Application.ActiveSheet.Name
> > Application.StatusBar = "Processing..." & loSheetName
> > MsgBox loSheetName
> > Set loCIM =
> > Application.Workbooks("apvomt_v5.xls").Worksheets("Detail")
> > If loCIM Is Nothing Then
> > MsgBox "Workbook not opened apvomt_v5.xls"
> > Exit Sub
> > End If
> > Set loSheet =
> > Application.Workbooks(loBookName).Worksheets(loSheetName)
> > '~~ Temp
> > If loSheet Is Nothing Then
> > MsgBox "Workbook not opened " & loBookName & " " & loSheetName
> > Exit Sub
> > End If
> > option
> > site.getConfig ("SH-451455")
> > cnt = 3
> > cimCnt = 7
> > '~~MsgBox site.s1dnAddr & "CIM=" & cim.s2BatchAddr
> > Do
> > '~~ Setup Invoice Value
> > loCIM.Range(cim.s2InvoiceAddr & cimCnt).Value = _
> > loSheet.Range(site.s1dnAddr & cnt).Value
> > '~~ Update counter
> > cimCnt = cimCnt + 1
> > cnt = cnt + 1
> > Loop While VBA.Trim(loSheet.Range(site.s1dnAddr & cnt)) <> ""
> > MsgBox cnt
> > End Sub
> >
> >
> > Bob Phillips wrote:
> > > On Error Resume Next
> > > SheetExists = CBool(Not Application.Workbooks("myWorkBook") _
> > > .Worksheets("myWorksheet") Is Nothing)
> > > On Error GoTo 0
> > > If SheetExists Then
> > > Set loWS =
> > > Application.Workbooks("myWorkBook").Worksheets("myWorksheet")
> > >
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (replace somewhere in email address with gmail if mailing direct)
> > >
> > > "moonhk" <moon_ils-(E-Mail Removed)> wrote in message
> > > news:(E-Mail Removed)...
> > > > How to check worksheets exist or not ?
> > > >
> > > > Dim loWS As Worksheet
> > > > Set loWS =
> Application.Workbooks("myWorkBook").Worksheets("myWorksheet")
> > > >
> >