VBA Macro - Copy and Paste date from Newest File Location

[Nickyashy]

Hi,

Please can anyone assist with a Macro. I need to be able to copy data on Sheet1 from the newest file in a file location and paste in a designated sheet on my Active File.

I can currently run a macro to open the latest file, but I want to just automate the copy and past exercise so that I can fully automate the whole report.

Example.

1. New Report
2. Copy Sheet1 from File X - which is located in C Drive location (newest file in the folder must be opened)
3. Paste Sheet1 to Sheet 4 on active open 'New Report'

The Macro I am currently using to open the newest file is written below, If this can be adapted to achieve the above I dont know.

Sub OpenLatestFile()


'Declare the variables
Dim MyPath As String
Dim MyFile As String
Dim LatestFile As String
Dim LatestDate As Date
Dim LMD As Date

'Specify the path to the folder
MyPath = "C:\Users\Chris\Documents\Excel VBA Test"

'Make sure that the path ends in a backslash
If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"

'Get the first Excel file from the folder
MyFile = Dir(MyPath & "*.xls", vbNormal)

'If no files were found, exit the sub
If Len(MyFile) = 0 Then
MsgBox "No files were found...", vbExclamation
Exit Sub
End If

'Loop through each Excel file in the folder
Do While Len(MyFile) > 0

'Assign the date/time of the current file to a variable
LMD = FileDateTime(MyPath & MyFile)

'If the date/time of the current file is greater than the latest
'recorded date, assign its filename and date/time to variables
If LMD > LatestDate Then
LatestFile = MyFile
LatestDate = LMD
End If

'Get the next Excel file from the folder
MyFile = Dir

Loop

'Open the latest file
Workbooks.Open MyPath & LatestFile

End Sub




Many Thanks for any Help.

 

[CharlesChuckieCharles]

Replace

Open the latest file
    Workbooks.Open MyPath & LatestFile
        
End Sub

With

Dim MyWorkBook As Workbook

Set MyWorkBook = ActiveWorkbook

'Open the latest file
Workbooks.Open MyPath & LatestFile

Copy ActiveWorkbook.Sheets("Sheet1").Cells
ActiveWorkbook.Saved = True
ActiveWorkbook.Close (False)

MyWorkBook.Sheets.Add
ActiveSheet.Range("a1").Paste xlPasteAll



End Sub

and test...

 

[Nickyashy]

Hi Thanks for this, it did not work.

It opens the file that I want to copy it from - is it possible that it doesnt have to open it?

Additionally from your code I can't see where I can add the sheet I want the data to be pasted to once it has been copied from its source location.

It says ActiveSheet.Range("a1!) - but I want it to go to a paste over a specific sheet within my master report.

This way I can run the macro's from one location without having to go in and out of sheets with front end validation to make sure the correct report has been grabbed.

Thanks again

 

[CharlesChuckieCharles]

Sorry for late reply

I assumed that the code is in a separate WKBK from the Data

Thus

Dim MyWorkBook As Workbook

#### Save the name of the Code wkb

Set MyWorkBook = ActiveWorkbook

'Open the latest file
Workbooks.Open MyPath & LatestFile

###### opening the workbook makes it automatically the active workbook


#### copy the entire sheet to the clipboard
Copy ActiveWorkbook.Sheets("Sheet1").Cells

'set just opened workbook so it doesn't ask to save
ActiveWorkbook.Saved = True

'close the just opened workbook
ActiveWorkbook.Close (False)

'## add a new sheet to the code wkb
MyWorkBook.Sheets.Add

'#### paste in the sheet from the clipboard into the new sheet (
ActiveSheet.Range("a1").Paste xlPasteAll

'BUT you could instead of add do
MyWorkBook.Sheets("MytargetsheetName").activate

ActiveSheet.Range("a1").Paste xlPasteAll


End Sub

Be aware that the PASTE target range needs to be the same size or bigger than the Source range

so when copying from the data sheet specify a range... Range("A1:G16")
Copy ActiveWorkbook.Sheets("Sheet1").Range("A1:G16")

hope this help