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

`Set wsCopy = Workbooks("Workbooks.Open MyPath & LatestFile").Worksheets("sheet1")` does not have any meaning. What do you want doing in this code line? Isn't it the line raising the error? — FaneDuru, Mar 24 '21 at 20:51
In Part 1: you need a workbook variable: `Dim wb As Workbook`, then change `Workbooks.Open MyPath & LatestFile` to `Set wb = Workbooks.Open(MyPath & LatestFile)`. Then in part 2, you can do `Set wsCopy = wb.Worksheets("sheet1")`. — BigBen, Mar 24 '21 at 20:51
After `Workbooks.Open MyPath & LatestFile` the active workbook becomes the newly open one. So you can write `Set wsCopy = ActiveWorkbook.Worksheets("sheet1")`, but doing that immediately after opening the workbook in discussion. — FaneDuru, Mar 24 '21 at 20:54

VBasic2008 · Accepted Answer · 2021-03-25T16:02:08.770

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.

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

1 Answers1

Referring to a Workbook