On Error Issues

  • Thread starter Thread starter cmk18
  • Start date Start date
C

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
 
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]...
 
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
 
Back
Top