Macro : Open Latest File in Folder and Copy Paste Below Last Cell

Question

How can i combine these two parts of code in Excel VBA?

Part 1:

Sub Copypastelastrow()

    Dim MyPath As String
    Dim MyFile As String
    Dim LatestFile As String
    Dim LatestDate As Date
    Dim LMD As Date
    Dim LMD As Variant
    Dim wsCopy As Worksheet
    Dim wsDest As Worksheet
    Dim lCopyLastRow As Long
    Dim lDestLastRow As Long

    MyPath = "C:\Users\andrew\Desktop\newdoc"

    If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"
    MyFile = Dir(MyPath & "*.xlsx", vbNormal)
    If Len(MyFile) = 0 Then
        MsgBox "No files were found...", vbExclamation
        Exit Sub
    End If
    Do While Len(MyFile) > 0

        LMD = FileDateTime(MyPath & MyFile)    
        If LMD > LatestDate Then
            LatestFile = MyFile
           LatestDate = LMD
        End If
        MyFile = Dir
    Loop
    Workbooks.Open MyPath & LatestFile

I don't understand how to set the opened workbook as active workbook and copy-paste below last row. I think The error is in Part 2: object variable or with block variable not set!

---

Part 2:

  Set wsCopy = Workbooks("Workbooks.Open MyPath & LatestFile").Worksheets("sheet1")
  Set wsDest = Workbooks("Workbook2").Worksheets("sheet1")
  
  lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "B").End(xlUp).Row
  lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "B").End(xlUp).Offset(1).Row
  
   wsCopy.Range("S3:T" & lCopyLastRow).Copy
    wsDest.Range("B" & lDestLastRow).PasteSpecial Paste:=xlPasteValues

Answer 1

Referring to a Workbook

• When you open a workbook, it becomes the ActiveWorkbook:

  Workbooks.Open MyPath & LatestFile
  Set wsCopy = ActiveWorkbook.Worksheets("Sheet1")
  

  or in one line:

  Set wsCopy  = Workbooks.Open(MyPath & LastestFile).Worksheets("Sheet1")
  

  To close the workbook later you will use:

  wsCopy.Parent.Close SaveChanges:=False ' usually because it's only read from.
  

• Using a variable:

  Workbooks.Open MyPath & LatestFile
  Dim wb As Workbook: Set wb = ActiveWorkbook
  Set wsCopy = wb.Worksheets("Sheet1")  
  

  or:

  Dim wb As Workbook
  Set wb = Workbooks.Open(MyPath & LatestFile)
  Set wsCopy = wb.Worksheets("Sheet1")  
  

  To close the workbook later you will use:

  wsCopy.Parent.Close SaveChanges:=False ' usually because it's only read from.
  

  or

  wb.Close SaveChanges:=False ' usually because it's only read from.