Help with a VBA code to copy a sheet to a new workbook

STEVENS3010

Board Regular
Joined
Feb 4, 2020
Messages
61
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi all,

Quick disclaimer - I am a complete VBA novice.

I've managed to put together a code following various youtube tutorials where the macro opens the most recent saved file in a specific folder. I'm now stuck on the next part of the code where I would like to copy the data from this active sheet on this open workbook into the book where the macro sits, and then to close the open workbook.

A possible complication is that the file being opened with the first part of the macro will be a new file every day, hence why I've coded for the most recently saved version rather than a static file name.

The code I've put together so far is below if it helps...

VBA Code:
Sub recentFileSpecificFolder()
Dim myFile As String, myRecentFile As String, myMostRecentFile As String
Dim recentDate As Date
Dim myDirectory As String
myDirectory = "\\my file path is here\"
Dim fileExtension As String
fileExtension = "*.xls"

If Right(myDirectory, 1) <> "\" Then myDirectory = myDirectory & "\"

myFile = Dir(myDirectory & fileExtension)

If myFile <> "" Then
    myRecentFile = myFile
    recentDate = FileDateTime(myDirectory & myFile)
Do While myFile <> ""
    If FileDateTime(myDirectory & myFile) > recentDate Then
        myRecentFile = myFile
        recentDate = FileDateTime(myDirectory & myFile)
    End If
    myFile = Dir
Loop
End If
myMostRecentFile = myRecentFile
Workbooks.Open Filename:=myDirectory & myMostRecentFile

End Sub
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Watch MrExcel Video

Forum statistics

Threads
1,130,185
Messages
5,640,679
Members
417,160
Latest member
Timon82

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top