On Error statement in VBA not working

On Error is working as designed; the issue is how the error handler is structured.

Key points about On Error in VBA:

1. On Error GoTo label must appear before the line that may raise the error, and the label must be a line label inside the same procedure.
2. When the error occurs, execution jumps to the label. If no error occurs, execution continues normally and will also fall through to the label unless you exit the procedure or branch around it.
3. On Error GoTo 0 turns error handling off; it does not “go to line 0”.

In the sample:

On Error GoTo NEXT
Sheets("MyTab").Select
On Error GoTo 0

NEXT:

Problems:

• NEXT is both a label and a VBA keyword (Next in loops). Use a different label name (for example, SheetHidden:) to avoid confusion.
• If Sheets("MyTab").Select fails, control jumps to the label; if it succeeds, execution continues and then falls through into the label code unless explicitly skipped.

A simple pattern to test visibility without relying on Select errors is to check the Visible property directly:

Sub TestSheetVisibility()
    Dim ws As Worksheet
    On Error GoTo SheetMissing

    Set ws = ThisWorkbook.Sheets("MyTab")
    On Error GoTo 0          ' sheet exists, turn off handler

    If ws.Visible = xlSheetVisible Then
        ' MyTab is visible
    Else
        ' MyTab is hidden or very hidden
    End If

    Exit Sub

SheetMissing:
    On Error GoTo 0
    ' Sheet MyTab does not exist
End Sub

If the goal is specifically to branch when Select fails, structure it like this:

Sub TestSelect()
    On Error GoTo HiddenOrCannotSelect

    Sheets("MyTab").Select
    On Error GoTo 0

    ' Code here runs if MyTab was successfully selected
    Exit Sub

HiddenOrCannotSelect:
    On Error GoTo 0
    ' Code here runs if MyTab is hidden, missing, or cannot be selected
End Sub

This uses On Error correctly: handler enabled before the risky line, disabled after, and a distinct label for the error-handling section.

References:

• Elements of run-time error handling
• On Error statement

Hi Albert

From your comments, scenario, and request, I understand the following:

1. You want to save the Excel file in the folder named "Counter Listings by Date" located in an unknown directory
2. Save the file under the name "Counter Listings - yyyy-mm-dd.xlsm". Where the date ( I assume would be the current date)

I created and tasted this code, which matches your requirements

a) The code loops through the Dir:\D to Dir:\G using the Chr function. For more info, please check the links below.

Chr function ------------ VBA Character set (0 - 127)

b) It checks if the "Counter Listings by Date" folder exists, if it does...

c) It then checks if the current file you want to save already exists.

d) If it doesn't. The code will finally save the file

NOTE:

If the "Counter Listings by Date" folder exists in more than one Directory, the macro will save a file in each folder

I hope this helps you and gives a solution to your problem

Do let me know if you need more help

Kind Regards

JeovanyCV

Here is the code.

Sub Saveit()
Dim K As Integer
Dim myPath As String
Dim myDate As String
Dim myFolderPath As String
Dim myfileName As String
Dim myFolderExist As String
Dim myFileExist As String


myDate = Format(Date, "yyyy-mm-dd") ''' The current date
myfileName = "\Counter Listings - " & myDate & ".xlsm"


For K = 68 To 71   ''' Meaning from dir: D: to G:
        myFolderPath = Chr(K) & ":\Counter Listings by Date"
        
        ''' Check if the folder exist in the directory:
        myFolderExist = Dir(myFolderPath, vbDirectory)
        If myFolderExist <> vbNullString Then
                Count = Count + 1
                myPath = myFolderPath & myfileName
        
        ''' Check if the file exist in the folder
                myFileExist = VBA.FileSystem.Dir(myPath)
                If myFileExist = VBA.Constants.vbNullString Then
                    With ActiveWorkbook
                        .SaveAs fileName:=myPath, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
                        .Protect Structure:=True, Windows:=False
                    End With
               End If
        End If
Next K
    '''Output/Result Messages
        If Count = 0 Then
            Beep
            MsgBox Title:="Dir: ERROR", Prompt:="The folder: = Counter Listings by Date," & vbNewLine & "Does not exist in any Directory"
         Else
            MsgBox Title:="FILE SAVED", Prompt:="The file has been successfully saved" & vbNewLine & "Job Done!"
        End If
End Sub

Yes, the Hidden problem was solved, but I also have another problem with OnError...

I want to save my file to a computer where I do not know what the directory is. So, I want to start at "D" and increment until a successful SaveAs is achieved.

On my computer, the directory for my thumb drive is "G". The following code works for the pass using Dir = "D", but

on the second pass with Dir = "E", it fails with the error message "Run-time error '76' Path not found

What am I missing?

Here is the code I am using:

Sub Saveit()

Dim Dir As String

Dim MyDate As String

MyDate = "2026-03-08'"

Dir = "D"               'Setting the path for SaveAs

NEXTE:

On Error GoTo NEXTF

ChDir Dir & ":\Counter Listings by Date"

On Error GoTo 0

ActiveWorkbook.SAVEAS Filename:= _

     Dir & ":\Counter Listings by Date\Counter Listings - " & MyDate & ".xlsm", FileFormat _

    :=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

ActiveWorkbook.Protect Structure:=True, Windows:=False

Exit Sub

NEXTF:

On Error GoTo 0

If Dir = "D" Then

   Dir = "E"

   GoTo NEXTE

ElseIf Dir = "E" Then

   Dir = "F"

   GoTo NEXTE

Else

   Dir = "G"

   GoTo NEXTE

End If

Beep

MsgBox "Error: Path not found for saving this file"

End Sub