What is the best way to copy worksheets from a closed workbook into the current open workbook? I've done a little digging around and found the below code but I don't see where it places the data? Also, I'd like to have an if loop to create new worksheets if needed.

Thanks

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("C1")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
    ActiveSheet.EnableCalculation = True
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationAutomatic
     
    With Application.FileSearch
        .NewSearch
        .LookIn = "C:\" ' Change the file path to your suite
        .Filename = [C1] & ".XLS"
         
        If .Execute(msoSortByFileName, msoSortOrderDescending, True) > 0 Then
            Workbooks.OpenText .FoundFiles(1), xlWindows
            ActiveWorkbook.Close
             
        End If
    End With
     
    ActiveSheet.EnableCalculation = False
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
     
End Sub