How can I stop the Program making new sheets, if this sheets (IGeneral,IInput,IResult) already exist?

Question

The VBA code goes through all excel files in the folder and checks if some specific sheets exist. but if I run the code 2nd time, it creates new sheets. How can I make it stop?

Sub LoopAllExcelFilesInFolder()
    Dim wb As Workbook
    Dim myPath As String
    Dim myFile As String
    Dim myExtension As String
    Dim FldrPicker As FileDialog
    Dim CurrentSheetName As String

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual

    Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)

    With FldrPicker
        .Title = "Select A Target Folder"
        .AllowMultiSelect = False
        If .Show <> -1 Then GoTo NextCode
        myPath = .SelectedItems(1) & "\"
    End With

NextCode:
    myPath = myPath
    If myPath = "" Then GoTo ResetSettings
    myExtension = "*.xls*"

    myFile = Dir(myPath & myExtension)

    Do While myFile <> ""
        Set wb = Workbooks.Open(Filename:=myPath & myFile)
        DoEvents
        CurrentSheetName = ActiveSheet.Name         
        Sheets.Add    

        On Error Resume Next     

        ActiveSheet.Name = "IGeneral"               
        CurrentSheetName = ActiveSheet.Name
        Sheets(CurrentSheetName).Select
        Sheets.Add      

        On Error Resume Next   

        ActiveSheet.Name = "IInput"         
        Sheets(CurrentSheetName).Select
        CurrentSheetName = ActiveSheet.Name
        Sheets.Add    

        On Error Resume Next     

        ActiveSheet.Name = "IResult"            
        Sheets(CurrentSheetName).Select                    

        wb.Close SaveChanges:=True              

        DoEvents
        myFile = Dir
    Loop

    MsgBox "Task Complete!"

ResetSettings:
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

Note that you use `On Error Resume Next` wrong. This line hides **all** error messages until `End Sub` but the errors still occur. With this line you just close your eyes if anything goes wrong. Therefore remove `On Error Resume Next` completely and fix your errors. • You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). • Also using `GoTo` is a very bad approach that you should avoid at any cost (unless it is used for an error handler). — Pᴇʜ, Jul 30 '19 at 06:55
[VBA Error Handling – A Complete Guide](https://excelmacromastery.com/vba-error-handling) — Pᴇʜ, Jul 30 '19 at 06:56
@ITDev do you want to stop the program if a sheet exists or go to next line if sheet IGeneral exists — TourEiffel, Jul 30 '19 at 08:05
@Dorian the Program should ignore every file that already has all 3( IGeneral, IInput, and IResult) and then the Program should close after checking everything. — ITDev, Jul 30 '19 at 14:08
@ITDev hope this [answer](https://stackoverflow.com/a/57274605/11167163) will Answer to your question, Please do not hesitate to give me **feedbacks** — TourEiffel, Jul 30 '19 at 14:56

Cyril · Answer 1 · 2019-07-30T14:15:16.183

If you want to determine if a sheet exists, and do something whether or not it is/isn't there, then you just need to determine if there's an error when you evaluate the first cell in the named sheet, e.g.:

If IsError(Evaluate("IGeneral!A1")) AND IsError(Evaluate("IInput!A1")) AND IsError(Evaluate("IResult!A1")) Then 'IsError returns boolean
    'Do something when there is an error (>=1 sheets DO NOT exist)
Else
    'Do something when there is no error (all sheets DO exist)
End If

Regarding your general coding, please review @Pᴇʜ 's comments below the post as there is a lot of important and useful info there.

Edit1: Adding in sheet names (IGeneral, IInput, and IResult)

Your entire other bit of code could even go into the True scenario so it only runs if one/any of those sheets do not exist.

How can I stop the Program making new sheets, if this sheets (IGeneral,IInput,IResult) already exist?

1 Answers1