I have been working on something that really has me stumped, even after searching the internet for days (although I must admit I'm still quite the novice.)

I want my code to allow the user to choose the folder (which will be a sub-folder within a network drive), then loop through all excel files in that folder and copy the data from a specific sheet (same name in each file - "SUBMITTED BUDGET SUMMARY") for each of those files and paste it into my workbook to create a master summary sheet.

Note: The copied data needs to be pasted into the MASTER SUMMARY sheet as a link, and should leave a blank line between the data pasted from each file. So copy the data from workbook1 and paste it into MASTER SUMMARY workbook, leave a blank row, then paste the data from workbook2, leave a blank row, and so on.

I've piece-mealed the code below from various searches and tested it using a folder on my desktop. It worked beautifully, but now it won't work when I try to choose the ACTUAL folder containing my files, which is a sub folder on a shared network drive.

Here's my code:

Code:
Option Explicit 
 
Sub merge_all__input_workbooks() 
     
    Dim wkbDest As Workbook 
    Dim wksDest As Worksheet 
    Dim wkbSource As Workbook 
    Dim wksSource As Worksheet 
    Dim MyPath As String 
    Dim MyFile As String 
    Dim FolderName As String 
     
    Application.ScreenUpdating = False 
    Application.DisplayAlerts = False 
     
    Set wkbDest = ThisWorkbook 
    Set wksDest = wkbDest.Worksheets("Sheet1") 
     
    With Application.FileDialog(msoFileDialogFolderPicker) 
        .AllowMultiSelect = False 
        .Show 
        On Error Resume Next 
        FolderName = .SelectedItems(1) 
        Err.Clear 
        On Error Goto 0 
    End With 
     
    MyPath = FolderName 
     
    If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\" 
     
    MyFile = Dir(MyPath & "*.xls") 
     
    Do While Len(MyFile) > 0 
        Set wkbSource = Workbooks.Open(MyPath & MyFile) 
        Set wksSource = wkbSource.Worksheets("SUBMITTED BUDGET SUMMARY") 
         
         'COPIES TABLE RANGE (table name = SUMMARY) FROM INPUT WORKBOOK SUBMITTED BUDGET SUMMARY PAGE AND PASTES IT TO THE END OF THE SUMMARY SHEET
        Sheets("SUBMITTED BUDGET SUMMARY").Select 
        Range("SUMMARY").Copy 
        Windows("MASTER BUDGET SUMMARY - ALL BUDGET OFFICERS.xlsm").Activate 
        With Sheets("Sheet1") 
            .Activate 
            .Cells(Rows.Count, 1).End(xlUp).Offset(2, 0).Select 
            .Paste Link:=True 
        End With 
         
        wkbSource.Close savechanges:=False 
        MyFile = Dir 
    Loop 
     
    Call format_all_summary_page 
    Call summary_pivot 
     
    Sheets("Sheet2").Select 
    Sheets("Sheet2").Name = "Pivot Table" 
    Sheets("Sheet1").Select 
    Sheets("Sheet1").Name = "Budget Summary Table" 
    Sheets("Pivot Table").Select 
     
    Application.ScreenUpdating = True 
    Application.DisplayAlerts = True 
     
     
    MsgBox "Completed...", vbInformation 
     
End Sub
I've looked everywhere at various possible solutions and I can't figure it out!!! Any help would be MUCH appreciated!

Also posted here because I'm desperately trying to figure this out ASAP: Choose folder, loop thru files to copy data & paste in new master workbook