How do I Copy a Worksheet from one Workbook to Ano

Question

I want to Copy one Sheet from a Workbook I'm opening via a FilePath into another Workbook that contains the Macro I'm running.

What I'm having trouble with is that Whenever I copy and Paste the files the Macro creates a new Workbook and pastes the data in the first sheet in that workbook. I was defining a specific sheet within the second workbook to paste the code so I'm unsure why the destination of my paste is a random workbook.

Public filepath As String

Sub FileOpenDialogBox()

'Display a Dialog Box that allows to select a single file.
'The path for the file picked will be stored in fullpath variable
  With Application.FileDialog(msoFileDialogFilePicker)
        'Makes sure the user can select only one file
        .AllowMultiSelect = False
        'Filter to just the following types of files to narrow down selection options
        .Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.xlsb", 1
        'Show the dialog box
        .Show

        'Store in fullpath variable
        fullPath = .SelectedItems.Item(1)
    End With
    filepath = fullPath
    'It's a good idea to still check if the file type selected is accurate.
    'Quit the procedure if the user didn't select the type of file we need.
    If InStr(fullPath, ".xls") = 0 Then
        Exit Sub
    End If

    'Open the file selected by the user
    'Workbooks.Open fullpath

End Sub

Sub CopySheet()
    'Module 1 FilePath import as Variable
    MsgBox filepath

    Dim spo_book As Workbook
    Dim target_book As Workbook
    Set spo_book = ActiveWorkbook
    Set target_book = Workbooks.Open(filepath)

    Dim dst_sheet As Worksheet
    Dim target_sheet As Worksheet
    Set dst_sheet = spo_book.Sheets("SPO Data")
    Set target_sheet = target_book.Sheets("Untimed Parts")
    target_sheet.Copy
    dst_sheet.Paste

End Sub

The expected outcome is that the Copy-Paste will Copy from the Chosen Workbook via my FileDialog into the Sheet called "SPO DATA' that I set as the variable dst_sheet, I thought it might've been a range issue and I tried to put in a range and it said the data didn't match so I went back to my sheet paste.

Answer 1

How about do it a bit old school :

    sub sample()

    Application.ScreenUpdating = false
    Sheet1="Willy"
    Sheet2="Wilma"

    for row1=20 to 500
    for col= 30 to 3300

    Sheets(Sheet1).Cells(row1, col1).Value=Sheets(Sheet2).Cells(row2, col2).Value
    Sheets(Sheet1).Cells(row1, col1).Formula=Sheets(Sheet2).Cells(row2, col2).Formula
    Sheets(Sheet1).Cells(row1, col1).Comment=Sheets(Sheet2).Cells(row2, col2).Comment

    next
    next
    Application.ScreenUpdating = True
    end sub

'cause somebody has asked how to address some sheets :)

  Sub ws_all()
  Dim wb As Workbook

For Each wb In Application.Workbooks
    Debug.Print wb.Name

    For Each ws In wb.Sheets

        Debug.Print ws.Name

    Next
Next

Debug.Print Application.Workbooks; ("Workbookname").Sheets  ("Sheetname").Name

End Sub