VBA Just Stops

[jondavis1987]

Below is where i have a code to create a worksheet if one does not exist. It's supposed to do the rest of the sub after this but it stops at either creating the sheet or locating the sheet if it already existed. What am i doing wrong?

'   Open destination workbook and capture it as destination workbook
    Workbooks.Open "C:\Users\jdavis\Dropbox\Quality Control\Asphalt\Mold Heights\" & destName & ".xlsx"
    Set destWB = ActiveWorkbook

 On Error GoTo ErrHandler:
    Worksheets(wsName).Activate
    Exit Sub

ErrHandler:
    If Err.Number = 9 Then
        ' sheet does not exist, so create it
        Worksheets.Add.Name = wsName
        ' go back to the line of code that caused the problem
       Resume
       End If
 
'   Find last row of data in desired worksheet of destination workbook
    lastRow = destWB.Sheets(wsName).Cells(Rows.Count, "A").End(xlUp).Row + 1

'   Copy Mold Heights data from source workbook to destination workbook
    srcWB.Sheets("A").Range("G3").Copy
    destWB.Sheets(wsName).Range("A" & lastRow).PasteSpecial xlPasteValues

 

[Joe4]

It is because of the "Exit Sub" you have in there.

The way to structure error handling is to put it at the very end of your code, and have the "Exit Sub" line just before that. If you put it in the middle, you run into the problems you are having.

 

[jondavis1987]

It is because of the "Exit Sub" you have in there.

The way to structure error handling is to put it at the very end of your code, and have the "Exit Sub" line just before that. If you put it in the middle, you run into the problems you are having.

Took me a while to understand what you meant and I got it working. This is how I did it

'   Open destination workbook and capture it as destination workbook
    Workbooks.Open "C:\Users\jdavis\Dropbox\Quality Control\Asphalt\Mold Heights\" & destName & ".xlsx"
    Set destWB = ActiveWorkbook
    
'   Error Route
    On Error GoTo ErrHandler:
    Worksheets(wsName).Activate

'   Find last row of data in desired worksheet of destination workbook
    lastRow = destWB.Sheets(wsName).Cells(Rows.Count, "A").End(xlUp).Row + 1

'   Copy Mold Heights data from source workbook to destination workbook
    srcWB.Sheets("A").Range("G3").Copy
    destWB.Sheets(wsName).Range("A" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("A").Range("E42").Copy
    destWB.Sheets(wsName).Range("B" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("A").Range("D18").Copy
    destWB.Sheets(wsName).Range("C" & lastRow).PasteSpecial xlPasteValues

'   Save changes and close destination workbook
    destWB.Close SaveChanges:=True

'   Export source workbook to PDF
    With srcWB
        fName = srcWB.Sheets("A").Range("A!F19").Value
        ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            "C:\Users\jdavis\Dropbox\Quality Control\Asphalt\Asphalt Reports\" & fName, Quality:=xlQualityStandard, _
            includeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
    End With


ErrHandler:
    If Err.Number = 9 Then
        ' sheet does not exist, so create it
        Worksheets.Add.Name = wsName
        ' go back to the line of code that caused the problem
       Resume
       End If
       Exit Sub
End Sub

 

[Joe4]

Not quite. You want the "Exit Sub" line above the error handler, like this:

Exit Sub

ErrHandler:
    If Err.Number = 9 Then
        ' sheet does not exist, so create it
        Worksheets.Add.Name = wsName
        ' go back to the line of code that caused the problem
       Resume
    End If

End Sub

The point is if you make it all the way through your code without encountering any errors, you want to exit the sub without going into the error handling code. Otherwise, it will run that error handling code at the very end, every time.

Besides, there is no point in having an "Ext Sub" line just before the "End Sub" line! That is redundant and unnecessary (no point in telling it to "Exit the sub" when the next line is the end of the sub).