Error Handler not working properly

[dahveedoff]

Hi,

I have got a script that keeps failing the second time around when it meets the condition for my error handler. So basically, the loop goes to look through a function sub procedure and then the value is parsed back into the main code and it checks to see if that value already exists as a named worksheet in the work book. As part of the process, there are other scripts that would have been run before to populate the workbook with named worksheets and this script needs to create a new worksheet after the earlier sheet.

e,g. If I had a sheet created earlier called CTS2, in this new script, I would need this new procedure to use the new name which is a result of the function sub procedure that transforms this worksheet into a value of CTS3 and then subsequently put this after CTS2.

The main issue is that I have new worksheets without any precursor worksheets e.g PT3 has no PT2 tab created earlier. So in order to trap this, I set up an error handler which works when it encounters a worksheet that meets this criteria but then fails when another worksheet about to be created has this same issue.

Do you think it has to do with resetting the error handler? I f so, i have done that twice in the code but stillno joy.

Please see the routines below

Main Code below

Do Until ActiveCell.Value = "" And ActiveCell.Offset(1, 0).Value = ""
   NewSheetName = FieldTrans(ActiveSheet.Range("B2").Value) & ActiveSheet.Range("C2").Value _
   & "_T3"
   SqlStr = NewSheetName
   Debug.Print SqlStr
' Error handling
On Error Resume Next
   On Error GoTo Sheetnotfound
   ActiveWorkbook.Sheets.Add After:=ActiveWorkbook.Sheets(FieldTrans(ActiveSheet.Range("B2").Value) & ActiveSheet.Range("C2").Value _
   & "_T2")
Sheetnotfound:
   If Err.Number = 9 Then
        'MsgBox ("Sheet name not found in " & ThisWorkbook.Name & " !")
           ActiveWorkbook.Sheets.Add After:=ActiveWorkbook.Sheets("Source3")
           Err.Clear
    End If
    On Error GoTo 0
   'Exit Sub
   ActiveSheet.Name = NewSheetName
   ActiveWorkbook.Sheets("Source3").Select
   ActiveSheet.Range("A1").Select
   ActiveCell.End(xlDown).Select
   ActiveCell.Offset(1, 0).Select
   B = ActiveCell.Row
   ActiveSheet.Range(Cells(1, 1), Cells(B, LastColumn)).Copy
   ActiveWorkbook.Sheets(NewSheetName).Select
   ActiveSheet.Range("A1").Select
   ActiveCell.PasteSpecial xlPasteAll
   ActiveWorkbook.Sheets("Source3").Select
   ActiveSheet.Range("A2").Select
   Do Until ActiveCell.Value = ""
        ActiveCell.EntireRow.Delete
   Loop
   ActiveCell.EntireRow.Delete

Loop

Function

If FN = "PG Accounting and Finance Portfolio BUS" Then
FNS = "A&F"
ElseIf FN = "PG MBA and PG Cert Management Portfolio BUS" Then
FNS = "MBA"
ElseIf FN = "PG Computer Science and Technology Portfolio CATS" Then
FNS = "CST"

etc...

 

[StephenCrump]

It is hard to understand what your code is doing, because of all the .Select, ActiveSheet etc references.

Can you perhaps give us more description of what your workbook looks like, and what you're trying to do. For example it's not clear:

- Whether when the code starts, ActiveSheet is "Source3" or some other sheet?
- When the code starts, where ActiveCell is?
- What rows you're deleting later with ActiveCell.EntireRow.Delete inside a loop
- Why your sheet names are always hardcoded with the suffix _T2 and _T3?
- Why your code always goes to SheetnotFound:

On Error GoTo Sheetnotfound
ActiveWorkbook.Sheets.Add After:=ActiveWorkbook.Sheets(FieldTrans(ActiveSheet.Range("B2").Value) & ActiveSheet.Range("C2").Value _
   & "_T2")
Sheetnotfound:

 

[StephenCrump]

Checking whether or not a sheet exists is a useful tool, which you may want to push out to a separate function. Here's one way you could code this:

Function SheetExists(sWorksheetName As String) As Boolean

    On Error Resume Next
        SheetExists = (Sheets(sWorksheetName).Name <> "")
    On Error GoTo 0

End Function

This then allows you to do something like this (where in your case it appears that "DefaultSheetName" is "Source3")?

Sub Test()

    Dim wsInsertAfter As Worksheet
    
    If SheetExists("SomeSheetName") Then
        Set wsInsertAfter = Sheets("SomeSheetName")
    Else
        Set wsInsertAfter = Sheets("DefaultSheetName")  'known to exist!
    End If
    
    Sheets.Add(After:=wsInsertAfter).Name = "YourNewSheetName"

End Sub

 

[Norie]

Why do you have both On Error Resume Next and On Error GoTo Sheetnotfound

 

[dahveedoff]

Hi Stephen, thanks for replying. Please bear in mind that this is not how I would normally write my codes. I started a role and met this clumpy script this way without any comments or methods of good practice, so I have had to deal with it on the go as I had a deliverable.

In repsonse to your Q's

Source3 is the starting sheet containing the source data.
Activecell is always B2 in this case
Once the code looks at the value in B", it goes into the sub routine and checks to see if it is within the list and if it is, it then equates it to a stringwhich is then used to name the new sheet that all rows with a value of B" are copied into. Once this is done, the script moves back to Source3 and deletes these values that were just copied until it gets to a new one.
They are hard coded that way to represent unit scores and some other stuf. can't change them.
I introduced "sheetnotfound" when I noticed that whoever wrote the code did not plan for the scenario i explained above in my earlier post.

I guess i might have added one too many error resets.. not too sure, hence the reason for posting this!

Thanks!

It is hard to understand what your code is doing, because of all the .Select, ActiveSheet etc references.

Can you perhaps give us more description of what your workbook looks like, and what you're trying to do. For example it's not clear:

- Whether when the code starts, ActiveSheet is "Source3" or some other sheet?
- When the code starts, where ActiveCell is?
- What rows you're deleting later with ActiveCell.EntireRow.Delete inside a loop
- Why your sheet names are always hardcoded with the suffix _T2 and _T3?
- Why your code always goes to SheetnotFound:

On Error GoTo Sheetnotfound
ActiveWorkbook.Sheets.Add After:=ActiveWorkbook.Sheets(FieldTrans(ActiveSheet.Range("B2").Value) & ActiveSheet.Range("C2").Value _
   & "_T2")
Sheetnotfound:

 

[dahveedoff]

Hi Norie,

Thought to catch any error in the sheets.add method for any sheet being creted that didnt have the "_T2" suffix already in the workbook by using the on next resume which would catch any possible error and then the if statement would analyse the error number and then start another script

 

[Norie]

I've never seen 2 On Error statements like that and I'm not sure what effect it will have on the code, if any.

 

[StephenCrump]

I've never seen 2 On Error statements like that and I'm not sure what effect it will have on the code, if any.

On Error Resume Next
On Error GoTo Sheetnotfound


'Effectively the same as:
On Error GoTo Sheetnotfound

i.e. first statement is immediately redundant.

 

[StephenCrump]

dahveedoff

It sounds like you want all "A&F" rows copied to an "A&F" sheet, all "MBA" rows to an "MBA" sheet etc etc, and then to delete the original data set.

I doubt the code as it's posted will achieve the right results, e.g. you're using a range based on the variable LastColumn which is not assigned a value, and you appear to be always pasting into cell A1 of Newsheet, i.e. overwriting values pasted from previous data rows.

I'm also assuming you have more than a few rows to process, which makes looping cell by cell inefficient.

A more efficient way to do this would be to use Autofilter to pick up all "A&F" rows and paste them, then pick up all "MBA" rows and paste them etc.

Here's some sample code you may be able to adapt: https://sites.google.com/a/madrocke...celassistant/parse-functions/sheet1-to-sheets

 

[Norie]

Stephen

That's what I thought but wasn't sure.