VBA Excel program works only with breakpoint

Question

This is my code for copying a sheet to new sheet. When I ran the program with breakpoint on Workbooks.Open(path) it was working correctly but when I ran without the breakpoint it simply opened the workbook without creating any sheet.
I have tried my best to rectify the error but I couldn't get the desired result.

Sub CopyCat()    

Dim ws As Worksheet
Dim no As Integer
Set ws1 = ActiveSheet
Dim path As String

temp_name = InputBox("Enter the Sheet No to be Created", "Enter the Value")

For Loop1 = 1 To ws1.UsedRange.Rows.Count
    path = Application.ActiveWorkbook.path & "\" & Application.WorksheetFunction.Trim(Trim(ws1.Cells(Loop1, 1).Value)) & " " & ws1.Cells(Loop1, 2).Value & ".xlsx"

    Set wb1 = Workbooks.Open(path)

    'ListBox1.AddItem wb.Name
    temp_name = "Sheet" & temp_name

    'error1 = CheckSheet(wb1, temp_name)
    'If (error1 <> True) Then
    ws1.Cells(4, 1).Value = "Created" & CStr(Loop1)
    Set ws = wb1.Worksheets(Sheets.Count)

    ws.Copy After:=wb1.Sheets(Sheets.Count)
    Set ws = ActiveSheet
    ws.Name = temp_name

    'Call PageSetting
    wb1.Close SaveChanges:=True
    ws1.Cells(4, 1).Value = "Created Done" & CStr(Loop1)
    'Else
    'wb1.Close SaveChanges:=True
    'End If
Next Loop1

End Sub


Function CheckSheet(ByVal wb As Workbook, ByVal sSheetName As String) As Boolean

Dim oSheet As Excel.Worksheet
Dim bReturn As Boolean

For Each oSheet In wb.Sheets

    If oSheet.Name = sSheetName Then
        bReturn = True
        Exit For
    End If

Next oSheet

CheckSheet = bReturn

End Function

Answer 1

This question is a bit vague, so i assumed a few things based on the code you provided.

You want to copy a worksheet from a workbook that runs the macro to another excel file.

All file names are listed in the source worksheet, column A - let's call it "Interface" worksheet.

You will need to add reference to Microsoft Scripting Runtime in your project for the FileSystemObject to work.

Code below isnt wery well written or optimised, yet it works.

Sub CopySht(NamesRange As Range, NameOfSheetToCopy As String)

Dim fso As FileSystemObject, oFile As File, fPath As String, fNamesArr() As Variant, fFolder As Folder
Set fso = New FileSystemObject

Dim InputWb As Workbook, InterfaceWs As Worksheet
Set InputWb = ThisWorkbook
Set InterfaceWs = InputWb.Worksheets("Interface")

Dim SheetToCopy As Worksheet
Set SheetToCopy = InputWb.Worksheets(NameOfSheetToCopy)

Set NamesRange = InterfaceWs.Range(NamesRange.Address)



fNamesArr() = NamesRange.Value

fPath = InputWb.path
Set fFolder = fso.GetFolder(fPath)

Dim i As Integer

For Each oFile In fFolder.Files
    For i = LBound(fNamesArr) To UBound(fNamesArr)
        If oFile.Name = fNamesArr(i, 1) & ".xls" Or oFile.Name = fNamesArr(i, 1) & ".xlsx" Then

            On Error Resume Next
            If Not (Workbooks(oFile.Name) Is Nothing) Then
                Workbooks(oFile.Name).Close SaveChanges:=False
            End If

            Workbooks.Open (oFile.path)

            If Not (CheckSheet(Workbooks(oFile.Name), SheetToCopy.Name)) Then
                SheetToCopy.Copy After:=Workbooks(oFile.Name).Sheets(1)
                Workbooks(oFile.Name).Close SaveChanges:=True
            End If

            If Not (Workbooks(oFile.Name) Is Nothing) Then
                Workbooks(oFile.Name).Close SaveChanges:=False
            End If

        End If
    Next i
Next oFile


End Sub

Function CheckSheet(ByVal wb As Workbook, ByVal sSheetName As String) As Boolean

    Dim oSheet As Excel.Worksheet
    Dim bReturn As Boolean

    For Each oSheet In wb.Sheets

        If oSheet.Name = sSheetName Then

            bReturn = True
            Exit For

        End If

    Next oSheet

    CheckSheet = bReturn

End Function

It doesnt matter if you pass NamesRange as qualified or unqualified range object, as shown below

Sub Wrapper()

    CopySht Range("A1:A6"), "CopyMe"
    'CopySht ThisWorkbook.Worksheets("Interface").Range("A1:A6"), "CopyMe"

End Sub