Copy data from a closed workbook and paste it in a specific sheet of an open workbook

[Mater]

Good day all,
This is my first post and I am looking for some help with VBA. I am new to coding and macros so what I have below has been copied from other posts on this site but it does't seem to work as I had hoped.

Desired function: I have a Macro Enabled Template workbook, "MODIFICATION TEMPLATE", with a few other macros on the first worksheet named "Command Sheet". I have created a Command Button "Import FSC" and what I had hoped it to do was to create a new sheet after "Command Sheet", and name it "FSC Temp Sheet". The code would then open a dialog box to the file location of the .xls source file in which I could select the most recent file downloaded from a website, allow me to select the workbook needed, copy the data from sheet 1, paste it into the newly created "FSC Temp Sheet" and lastly close the source sheet without saving. Disclosure: I download a new copy of the source sheet each week so both the workbook and worksheet change each time which is why I chose to copy "Sheet 1".
Problem: The below code works perfectly (after much trial, frustration and reading). As I step through each line of the code, a new sheet is created and named correctly, the dialog box opens and I can select the file I want to copy, the .xls file opens momentarily, then switches back to the "MODIFICATION TEMPLATE" workbook and the "FSC Temp Sheet", and finally closes the source sheet but the data is not pasted into the "FSC Temp Sheet".

Lastly, I chose this method mainly because I don't know any better but ultimately once the data is in the FSC Temp Sheet, I can then run another macro to modify the rows and columns as I need for analysis.

I apologize for the long winded post but thought it was better to over explain. Any help or better suggestions would be greatly appreciated.
Thank you
Mater

Option ExplicitSub Paste_New_FSC_Data()
    'Opens a new worksheet for FSC data to be copied to
    Dim ws As Worksheet
    With ThisWorkbook
        Set ws = .Sheets.Add(After:=.Sheets(.Sheets.Count))
        ws.Name = "FSC Temp Sheet"
        Worksheets("FSC Temp Sheet").Activate
    End With
    ' Opens the FSC weekly folder to select current weeks FSC Download


    Dim wbThisWB    As Workbook
    Dim wbImportWB  As Workbook
    Dim strFullPath As String
    Dim lngLastRow  As Long
    Dim lngLastCol  As Long
    
    Set wbThisWB = ThisWorkbook
    
    With Application.FileDialog(msoFileDialogFilePicker)
        .Filters.Clear
        .AllowMultiSelect = False
        .Title = "Please select most recent FSC Download:"
        .Filters.Add "Excel files", "*.xls", 1
        .InitialFileName = "H:\MY WORKING FILES\Daves Weekly FSC Files"
        .Show
        On Error Resume Next 'In case the user has clicked the button
            strFullPath = .SelectedItems(1)
            If Err.Number <> 0 Then
                wbThisWB = Nothing
                Exit Sub 'Error has occurred so quit
            End If
        On Error GoTo 0
    End With
    
    Application.ScreenUpdating = False
    
    Set wbImportWB = Workbooks.Open(strFullPath)
    'code here to copy and paste tab from Import WB into the current workbook
    On Error Resume Next 'In case there's no data or tab doesn't exist
        With wbImportWB.Sheets("Sheet 1")
            lngLastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            lngLastCol = .Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
            If lngLastRow > 0 And lngLastCol > 0 Then
                'If the 'lngLastRow' and 'lngLastCol' variable have been set there's data to be copied.
                'The following copies the entire range from tab 'Extract' in the import workbook to cell A1 in 'Sheet1' of this workbook (change to suit).
                Range(.Cells(1, 1), .Cells(lngLastRow, lngLastCol)).Copy wbThisWB.Sheets("Sheet 1").Cells(1, 1)
            End If
        End With
    On Error GoTo 0
    
    wbImportWB.Close False 'Close the Import WB without saving any changes.
    
    Set wbThisWB = Nothing
    Set wbImportWB = Nothing
    
    Application.ScreenUpdating = True
    
End Sub

 

[Mater]

Nice catch, but honestly couldn't tell you if there should be a space or not. I removed the space but it didn't have an effect.

 

[Mater]

So, this may not be working for whatever the reason, do you know of another way to skin this cat instead of using the lngLast command?

 

[mumps]

If no data was found then lngLastRow and lngLastCol should return a 1.
Is there any way you could upload the files (de-sensitized if necessary) as I described in Post #4 perhaps from another computer (home or work)?

 

[Mater]

I will have to take a bit of time to make some significant changes. You are only looking for the sheet with the data I need to copy, correct? Or a sample of everything I am working with? And i will have to send it from my personal computer later this evening. I am not in a hurry to find the solution so I can make the necessary modifications and post it to Dropbox this evening.
Thank you
Mater

 

[mumps]

That is correct, just the sheet with the data you need to copy. Don't forget to include any macros you are currently using if possible. Also the file names and sheet names should be the original names.

 

[Mater]

Will do, I can sanitize the info this evening and post it late tonight or tomorrow.

Thanks again for the assistance mumps, I appreciate you taking the time to help.
Mater