VBA - How to copy data and paste to current sheet?

[helpneeded2]

I am not really experienced with VBA, and am trying to copy from data from a source sheet in a different Excel file, and paste it via a button executed macro into the current spreadsheet.

Based on examples I have seen, I have been able to do it this way:

Sub Obtain_OLG_Data()
    Workbooks.Open ("G:\Branch1\Department2\Unit\Protected Document\OLG Master Lists\OLG MASTER TO DATE.xlsx")
    Workbooks("OLG MASTER TO DATE.xlsx").Worksheets("OLG MASTER CSV V10").Range("A1:AP260000").Copy
    Workbooks("OLG Sheet.xlsm").Worksheets("OLG_MASTER").Range("A2").PasteSpecial Paste:=xlPasteValues
End Sub

However, since the "OLG Sheet.xlsm" will always be renamed with the date as part of the name, (i.e.: OLG Sheet 2022-09-21.xlsm), I instead need to target the current sheet that the macro is launching from. Could someone please tell me how I can switch the focus back to main sheet, regardless of its name.

 

[helpneeded2]

8 hours later and I am still bashing my head against the wall on this one. I just can't figure out how to do this. Can someone please help.

Here is my latest attempt:

Sub Obtain_OLG_Patron_Data()
    Dim wbkSource As Workbook
    ' Set wbkSource = Workbooks("OLG Sheet.xlsm")
    Set wbkSource = ThisWorkbook.Name
  
    ' Get Open and copy data from OLG MASTER CSV File
    Workbooks.Open ("G:\Department\Group\Unit\Protected Document\OLG Master Lists\OLG MASTER TO DATE.xlsx")
    Workbooks("OLG MASTER TO DATE.xlsx").Worksheets("OLG MASTER CSV V10").Range("A1:AP260000").Copy
  
    ' Paste Data into OLG Sheet
    wbkSource.Worksheets("OLG_MASTER").Range("A2").PasteSpecial Paste:=xlPasteValues
  
    ' Copy cell value to quickly clear clipboard
    wbkSource.Worksheets("OLG_MASTER").Range("A2").Copy
  
    ' Close OLG MASTER CSV File
    Workbooks("OLG MASTER TO DATE.xlsx").Close SaveChanges:=False
  
    ' Return focus to OLG Sheet, and cancel copy mode
    wbkSource.Worksheets("OLG_MASTER").Range("A2").Select
    Application.CutCopyMode = False
End Sub

I believe somehow I need to incorporate ThisWorkbook into the code and I have tried Dim'ing wbkSource as a String and also a Workbook, but regardless I keep getting an error -- I know my syntax is likely wrong, but I just don't know enough about VBA to fix it. Please help.

 

[helpneeded2]

I'm not sure if this is the right/best way to do this, but I believe I have now found a way to make this work. I needed to just reference ThisWorkbook, and not ThisWorkbook.Name

Sub Obtain_OLG_Patron_Data()
    Dim wbkSource As Workbook
    Dim endOfRows As Long
    Set wbkSource = ThisWorkbook
    
    ' Open, find last row of data, and copy data from OLG MASTER CSV File
    Workbooks.Open ("G:\Department\Division\Unit\Protected Document\OLG Master Lists\OLG MASTER TO DATE.xlsx")
    endOfRows = ActiveSheet.Range("A:A").Find("*", , xlValues, , xlByRows, xlPrevious).Row
    Range("A1").Resize(endOfRows, 42).Select
    Selection.Copy
    
    ' Paste Data into OLG Sheet
    wbkSource.Worksheets("OLG_MASTER").Range("A2").PasteSpecial Paste:=xlPasteValues
    
    ' Copy cell value to quickly clear clipboard
    wbkSource.Worksheets("OLG_MASTER").Range("A2").Copy
    
    ' Close OLG MASTER CSV File
    Workbooks("OLG MASTER TO DATE.xlsx").Close SaveChanges:=False
    
    ' Return focus to OLG Sheet, and cancel copy mode
    wbkSource.Worksheets("OLG_MASTER").Range("A2").Select
    Application.CutCopyMode = False
End Sub