Looking for Last Updated File and importing a specific sheet

[Lloydmp]

Hi there,

I have a bit of code that automatically looks for the last edited file and opens it.
It looks at the current folder and the previous folder in the file structure which is important.

What i would like to add is some code to open/copy/mirror a specific sheet into another workbook.

my code is as follow:

 Private Sub Workbook_Open()
Dim fdr, fdr2 As Scripting.Folder
  Dim target As Scripting.File
    For Each fdr In CreateObject("Scripting.FileSystemObject").GetFolder("M:\COMMUNAL\Run Outs\RUN OUTS 2018").SubFolders
        On Error Resume Next
        For Each target In fdr.Files
            If InStr(1, UCase(target), UCase(".xls")) > 0 Then
                If target.DateLastModified > dteFile Then
                    dteFile = target.DateLastModified
                    strFile = target
                End If
            End If
        Next
    Next
    DateFirstFile = dteFile
    FirstFile = strFile
    For Each fdr2 In CreateObject("Scripting.FileSystemObject").GetFolder("M:\COMMUNAL\Run Outs\RUN OUTS 2018").SubFolders
        On Error Resume Next
        For Each target In fdr2.Files
            If InStr(1, UCase(target), UCase(".xls")) > 0 Then
                If target.DateLastModified > dteFile Then
                    dteFile = target.DateLastModified
                    strFile = target
                End If
            End If
        Next
    Next
    DateSecondFile = dteFile
    SecondFile = strFile
    If DateFirstFile >= DateSecondFile Then
    If Len(Dir(FirstFile)) Then Workbooks.Open FirstFile
    ElseIf DateSecondFile >= DateFirstFile Then
    If Len(Dir(SecondFile)) Then Workbooks.Open SecondFile
    End If
End Sub

I'm a complete novice so any advice is helpful.
Thanks,

Lloyd

 

[Andrew Fergus]

Hello
It appears you are comfortable with VBA so here is a link to another thread which shows how to do this.
I'm guessing you should be ok to adapt the solution to your situation?
Andrew

https://www.mrexcel.com/forum/excel-questions/532242-vba-copy-worksheet-workbook-another.html

 

[Lloydmp]

Thanks for this, Ill see how i get on adapting the code!
Lloyd