Open most recent file in folder, unmerge, copy, go to different file, paste. Need code, please help

[Greg_ismyusername]

Would be very grateful to anyone who can provide assistance.

I use a program that exports data to an xlsx file in a particular directory.

The code below will open the most recent file in the hardcoded directory.


Sub NewestFile()
Dim MyPath As String
Dim MyFile As String
Dim LatestFile As String
Dim LatestDate As Date
Dim LMD As Date
MyPath = "C:\Users\gregs\Dropbox\Financials Export"
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


From here, I would like the macro to navigate to sheet "Cash Flow" and unmerge all cells, select all, copy.

The macro will be stored in a separate workbook called "Macros_Financial Analysis"

in '[Macros_Financial Analysis.xlsm]Sheet1'!$A$1 will be the path and filename of the paste destination.

Example C:\Users\gregs\Dropbox\123 Main Street\Financials\123 Main Street Financials_v##.xlsx

123 Main Street Financials_v##.xlsx will have a sheet named Cash Flow, which is the paste destination.

So basically, the macro, triggered from my macro workbook will open most recent file in particular directory, unmerge, copy, open a workbook in a different directory as specified by a cell in my macro workbook and then paste.

Beyond my ability and would sincerely appreciate some help.

Thanks

 

[Greg_ismyusername]

Would be very grateful to anyone who can provide assistance.

I use a program that exports data to an xlsx file in a particular directory.

The code below will open the most recent file in the hardcoded directory.


Sub NewestFile()
Dim MyPath As String
Dim MyFile As String
Dim LatestFile As String
Dim LatestDate As Date
Dim LMD As Date
MyPath = "C:\Users\gregs\Dropbox\Financials Export"
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


From here, I would like the macro to navigate to sheet "Cash Flow" and unmerge all cells, select all, copy.

The macro will be stored in a separate workbook called "Macros_Financial Analysis"

in '[Macros_Financial Analysis.xlsm]Sheet1'!$A$1 will be the path and filename of the paste destination.

Example C:\Users\gregs\Dropbox\123 Main Street\Financials\123 Main Street Financials_v##.xlsx

123 Main Street Financials_v##.xlsx will have a sheet named Cash Flow, which is the paste destination.

So basically, the macro, triggered from my macro workbook will open most recent file in particular directory, unmerge, copy, open a workbook in a different directory as specified by a cell in my macro workbook and then paste.

Beyond my ability and would sincerely appreciate some help.

Thanks

sorry for the formatting :

Sub NewestFile()

    Dim MyPath As String
    Dim MyFile As String
    Dim LatestFile As String
    Dim LatestDate As Date
    Dim LMD As Date
    
    MyPath = "C:\Users\gregs\Dropbox\_Blatteis and Schnur\415 N. Crescent\Crescent Financials\"
    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