excel - How to reference an open workbook with a macro that is stored in my "Personal.xlsb" workbook?

Question

I am trying to copy data from any previously made workbook. My macro works as far as selecting the file to copy from and up until the paste line. It fails at ThisWorkBook.Worksheets("Imported Data").Range("A1").PasteSpecial xlPasteValues The "Imported Data" is sheet 4 in the currently open workbook. I believe the problem might be that the macro is running from my "Personal.xlsb" workbook (which is blank) so it cant find the correct sheet to paste to. How would I reference the current open workbook if the name changes each time a new one is made.

Sub Get_Data_From_File()

    Dim FileToOpen As Variant
    Dim OpenBook As Workbook

    Application.ScreenUpdating = False
    FileToOpen = Application.GetOpenFilename(Title:="Browse for your file 
    & Import Range", FileFilter:="Excel Files(*.xlsx*),*xlsx*")
    If FileToOpen <> False Then
        Set OpenBook = Application.Workbooks.Open(FileToOpen)
        OpenBook.Sheets(3).Range("A1:F27").Copy
        ThisWorkBook.Worksheets("Imported 
    Data").Range("A1").PasteSpecial xlPasteValues
        OpenBook.Close False
    
    End If
Application.ScreenUpdating = True

End Sub

Answer 1

Just assume it, but do it prior to your call to Application.Workbooks.Open() because that changes the current workbook.

Before you set OpenBook, set a new variable Set TargetRange = Worksheets("Imported Data").Range("A1"). (or you could use ActiveWorkbook.Worksheets...) Then you can change

ThisWorkBook.Worksheets("Imported Data").Range("A1").PasteSpecial xlPasteValues

to

TargetRange.PasteSpecial xlPasteValues