VBA Just Stops

jondavis1987

Active Member
Joined
Dec 31, 2015
Messages
411
Office Version
  1. 2019
Platform
  1. Windows
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?

VBA Code:
'   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
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,470
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Dec 31, 2015
Messages
411
Office Version
  1. 2019
Platform
  1. Windows
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

VBA Code:
'   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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,470
Office Version
  1. 365
Platform
  1. Windows
Not quite. You want the "Exit Sub" line above the error handler, like this:
VBA Code:
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).
 

Watch MrExcel Video

Forum statistics

Threads
1,118,972
Messages
5,575,302
Members
412,654
Latest member
dibison18
Top