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
Bookmarks