How to copy data from source workbook to current workbook through Application.FileDialog(msoFileDialogFilePicker)

Question

I am trying to copy data from my source workbook by using FileDialog(msoFileDialogFilePicker).

but the code is always stuck at "sourceworkbook.Worksheets("sheet1").Activate", I assume it is BCS the selected file is too big so the Micro is not able to activate the correct sheet while it's still in the process of opening. hence I added a time to wait for the file completely open, which still failed.

here is what I got,

Sub Test()
Dim sourceworkbook As Workbook
Dim currentworkbook As Workbook
Set currentworkbook = ThisWorkbook
Set sourceworkbook = Application.ActiveWorkbook
With Application.FileDialog(msoFileDialogFilePicker)
    .Filters.Clear
    .Filters.Add "Excel 2007-13", "*.xlsx; *.xlsm; *.xlsa"
    .AllowMultiSelect = False
    .Show
    If.SelectedItems.Count > 0 Then

        Starting_Time = Timer
        Application.Workbooks.Open.SelectedItems(1)
        Total_Time = Timer - Starting_Time
        Application.Wait (Total_Time)

        sourceworkbook.Worksheets("sheet1").Activate
        sourceworkbook.Worksheets("sheet1").Range("D4:CM60000").Copy
        currentworkbook.Worksheets("sheet2").Activate

        Starting_Time = Timer
        currentworkbook.Worksheets("sheet2").Cell("A1").Paste
        Total_Time = Timer - Starting_Time
        Application.Wait (Total_Time)

        Application.CutCopyMode = False
        sourceworkbook.Close
    End If
End With
Set sourceworkbook = Nothing
Set currentworkbook = Nothing
ThisWorkbook.Activate
Worksheets("sheet1").Activate
Worksheets("sheet1").Calculate
Worksheets("sheet1").Range("A2").Select
End Sub

Answer 1

Try this: comments in-line. Note there should be no need to Wait - typically VBA does not continue until the (eg) open/paste operations are complete.

Sub Test()
    Dim sourceworkbook As Workbook
    Dim currentworkbook As Workbook, Starting_Time, total_time
    
    Set currentworkbook = ThisWorkbook
    
    With Application.FileDialog(msoFileDialogFilePicker)
        .Filters.Clear
        .Filters.Add "Excel 2007-13", "*.xlsx; *.xlsm; *.xlsa"
        .AllowMultiSelect = False
        .Show
        If .SelectedItems.Count > 0 Then
    
            Starting_Time = Timer
            'Get a reference while opening the file
            Set sourceworkbook = Application.Workbooks.Open(.SelectedItems(1))
            WaitSecs Timer - Starting_Time
            
            Starting_Time = Timer
            sourceworkbook.Worksheets("sheet1").Range("D4:CM60000").Copy _
                currentworkbook.Worksheets("sheet2").Range("A1")
            WaitSecs Timer - Starting_Time
            
            Application.CutCopyMode = False
            sourceworkbook.Close savechanges:=False
        End If
    End With
    
    currentworkbook.Activate
    With currentworkbook.Worksheets("sheet1")
        .Activate
        .Calculate
        .Range("A2").Select
    End With
End Sub

'wait for `secs` seconds
Sub WaitSecs(secs)
    Application.Wait Now + secs * (1 / 24 / 60 / 60)
    Debug.Print "waited " & secs & " seconds"
End Sub