1

I'm trying to combine these two macros:

Macro 1: This finds a file and copies the sheet to another workbook. I need to find the latest file with the prefix "Car Assignments", this only works if I manually type the full file name.

Sub cpy()
    Dim x As Workbook
    Dim y As Workbook
    
    '## Open both workbooks first:
    Set x = Workbooks.Open("\\Desktop\ARC Dev\New folder\Car Assignments 11-16-2020.xlsx")
    Set y = Workbooks.Open("\\Desktop\ARC Dev\arcgoat.xlsm")
    
    'Now, copy what you want from x:
    x.Sheets("Portfolio Assignments").Range("A1:U920").Copy
    
    'Now, paste to y worksheet:
    y.Sheets("Portfolio Assignments").Range("A1").PasteSpecial
    
    'Close x:
    x.Close
    MsgBox ("Completed")
End Sub

Macro 2: This finds the most recent file in the directory, however, it does not account for files with the prefix 'Car Assignments'.

Option Explicit
    
Sub NewestFile()
    
    Dim MyPath As String
    Dim MyFile As String
    Dim LatestFile As String
    Dim LatestDate As Date
    Dim LMD As Date
        
    MyPath = "\\Desktop\ARC Dev\New folder\"
    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
    
End Sub

I am trying to copy the latest Excel file in "\Desktop\ARC Dev\New folder" directory that has the name "Car Assignments" in it, and then copy that sheet to a workbook called arcgoat in the "\Desktop\ARC Dev\arcgoat.xlsm" directory.

ZygD
  • 22,092
  • 39
  • 79
  • 102
Amateurhour35
  • 95
  • 1
  • 5
  • Does [this question](https://stackoverflow.com/questions/25490868/finding-latest-file-in-a-folder-and-opening-it-vba-access) help? That returns a file object, which you can use in a function. – BruceWayne Nov 23 '20 at 22:41
  • Thanks for that -- that specific thread actually helped me get the second macro working earlier today. The problem is slightly different though because I'm hoping to find/copy the sheet from the most recent file with the prefix "Car Assignments", not just most recent xlsx in the overall directory if that makes sense. – Amateurhour35 Nov 23 '20 at 23:26

1 Answers1

1

I have modified the code as you like ,

Sub NewestFile()

Dim MyPath As String
Dim MyFile As String
Dim LatestFile As String
Dim LatestDate As Date
Dim LMD As Date
Dim x As Workbook 'content copied to this work book
Dim y As Workbook 'y latest work book,where the content copied from

Set x = ActiveWorkbook

'MyPath = ActiveWorkbook.Path & "\files\" ' x workbook_location/files - Search the latest file y  here

MyPath = "C:\Users\siddharth\Desktop\ARC Dev\New folder\" 'search the y workbook here

If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"
MyFile = Dir(MyPath & "Car Assignments*.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

Set y = Workbooks.Open(MyPath & LatestFile)
y.Sheets("Portfolio Assignments").Range("A1:U920").Copy

Application.DisplayAlerts = False 'Error msg pop disable
x.Sheets("Portfolio Assignments").Range("A1").PasteSpecial

y.Close

Application.DisplayAlerts = True ' Error msg enabled back

MsgBox ("Content Copied")

End Sub
Siddhu
  • 48
  • 6
  • So I ran your script and it's prompting the no files were found message. The directory in which the latest Car Assignments files are located is \\Desktop\ARC Dev\New folder\ -- Maybe i don't understand VBA well enough, but I don't see where that directory is specified to know where to look for the latest files? Also the file in which we are pasting the data we copy is : "\\Desktop\ARC Dev\arcgoat.xlsm" in the sheet called "Portfolio Assignments" – Amateurhour35 Nov 24 '20 at 02:12
  • Code is updated , check it. and Mention full path name in Mypath @Amateurhour35 – Siddhu Nov 24 '20 at 02:28