Hi I was wondering if anyone knows how I can copy data from open file (one I am currently in) and paste into a specific file - paste in two sheets within the file.
I already have a code which will open the most recent file from a directory, but I then want it to select all the data in column A, copy it and paste it into my 'master sheet' - pasting the data in two of the sheets I specify in the master document,
but before pasting the whole of the sheets it goes into needs to be cleared so the new data will start in cell A1 and go down from there (basically the cells they were in before but in the master document)
I will also need to make the code I already have (below) search every 20 seconds and not return the Msg box if there isnt any but search again, if anyone could help with that too it would be really appreciated.
Thanks
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 = "C:\new folder"
If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"
MyFile = Dir(MyPath & "*.LIF", 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