On Error Issues

[cmk18]

I'm having issues capturing an error and using it to direct the code. Here is the code I'm trying to execute:

Public Sub sheetCheck()
cNodes = ThisWorkbook.Names("Nodes").RefersToRange.Rows.Count
For i = 1 To cNodes
On Error GoTo NewSheet
isThere = True
sheetName = sheetBase & i
Sheets(sheetName).Visible = False
check: If Not isThere Then Sheets("Scoring Sheet 0 (2)").Name = sheetName
Next i
Exit Sub
NewSheet: Scoring_0.Copy after:=Scoring_0
isThere = False
GoTo check
End Sub

Nodes refers to a list of nodes for this system. The sheets are named Scoring x, where x would be replaced by i in the loop. The code will execute perfectly through one error, but if I have more than one non-existent sheet, it fails.

Cheers-
Chris

[STEVE BELL]

I think you need to reset the error check

On Error goto 0

place this after
NewSheet:

--
steveB

Remove "AYN" from email to respond
"cmk18" <[email protected]> wrote in
message news:[email protected]...
>
> I'm having issues capturing an error and using it to direct the code.
> Here is the code I'm trying to execute:
>
> Public Sub sheetCheck()
> cNodes = ThisWorkbook.Names("Nodes").RefersToRange.Rows.Count
> For i = 1 To cNodes
> On Error GoTo NewSheet
> isThere = True
> sheetName = sheetBase & i
> Sheets(sheetName).Visible = False
> check: If Not isThere Then Sheets("Scoring Sheet 0 (2)").Name =
> sheetName
> Next i
> Exit Sub
> NewSheet: Scoring_0.Copy after:=Scoring_0
> isThere = False
> GoTo check
> End Sub
>
> Nodes refers to a list of nodes for this system. The sheets are named
> Scoring x, where x would be replaced by i in the loop. The code will
> execute perfectly through one error, but if I have more than one
> non-existent sheet, it fails.
>
> Cheers-
> Chris
>
>
> --
> cmk18
> ------------------------------------------------------------------------
> cmk18's Profile:
> http://www.excelforum.com/member.php...fo&userid=6047
> View this thread: http://www.excelforum.com/showthread...hreadid=386540
>

[Chip Pearson]

Once an error is raised, VBA is operating in "error mode". You
need to cause it to resume in "normal mode" by using a Resume
statement. Change your

GoTo check
' to
Resume check


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"cmk18" <[email protected]>
wrote in message
news:[email protected]...
>
> I'm having issues capturing an error and using it to direct the
> code.
> Here is the code I'm trying to execute:
>
> Public Sub sheetCheck()
> cNodes = ThisWorkbook.Names("Nodes").RefersToRange.Rows.Count
> For i = 1 To cNodes
> On Error GoTo NewSheet
> isThere = True
> sheetName = sheetBase & i
> Sheets(sheetName).Visible = False
> check: If Not isThere Then Sheets("Scoring Sheet 0 (2)").Name
> =
> sheetName
> Next i
> Exit Sub
> NewSheet: Scoring_0.Copy after:=Scoring_0
> isThere = False
> GoTo check
> End Sub
>
> Nodes refers to a list of nodes for this system. The sheets
> are named
> Scoring x, where x would be replaced by i in the loop. The
> code will
> execute perfectly through one error, but if I have more than
> one
> non-existent sheet, it fails.
>
> Cheers-
> Chris
>
>
> --
> cmk18
> ------------------------------------------------------------------------
> cmk18's Profile:
> http://www.excelforum.com/member.php...fo&userid=6047
> View this thread:
> http://www.excelforum.com/showthread...hreadid=386540
>

[Dave Peterson]

You could use a separate function that checks for existence of the worksheet.

It might make it easier to read your code when you come back to it, too.

Function WorksheetExists(SheetName As Variant, _
Optional WhichBook As Workbook) As Boolean
'from Chip Pearson
Dim WB As Workbook
Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook)
On Error Resume Next
WorksheetExists = CBool(Len(WB.Worksheets(SheetName).Name) > 0)
End Function

Public Sub sheetCheck()
cNodes = ThisWorkbook.Names("Nodes").RefersToRange.Rows.Count
For i = 1 To cNodes
sheetName = sheetBase & i
if worksheetsexists(sheetname, thisworkbook) then
'it exists
else
'it doesn't exist
end if
next i

End Sub

cmk18 wrote:
>
> I'm having issues capturing an error and using it to direct the code.
> Here is the code I'm trying to execute:
>
> Public Sub sheetCheck()
> cNodes = ThisWorkbook.Names("Nodes").RefersToRange.Rows.Count
> For i = 1 To cNodes
> On Error GoTo NewSheet
> isThere = True
> sheetName = sheetBase & i
> Sheets(sheetName).Visible = False
> check: If Not isThere Then Sheets("Scoring Sheet 0 (2)").Name =
> sheetName
> Next i
> Exit Sub
> NewSheet: Scoring_0.Copy after:=Scoring_0
> isThere = False
> GoTo check
> End Sub
>
> Nodes refers to a list of nodes for this system. The sheets are named
> Scoring x, where x would be replaced by i in the loop. The code will
> execute perfectly through one error, but if I have more than one
> non-existent sheet, it fails.
>
> Cheers-
> Chris
>
> --
> cmk18
> ------------------------------------------------------------------------
> cmk18's Profile: http://www.excelforum.com/member.php...fo&userid=6047
> View this thread: http://www.excelforum.com/showthread...hreadid=386540

--

Dave Peterson