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

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,286
Office Version
  1. 365
Platform
  1. Windows
Where do you want to copy the data to?
 

STEVENS3010

Board Regular
Joined
Feb 4, 2020
Messages
61
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
In to the workbook where the macro sits, file name Open.sheet.xlsm if it helps?

So essentially the code is opening a workbook where the data sits, copying the data, closing the work book and then pasting into the starting work book.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,286
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Where in the starting workbook should the data be copied to?

A specific worksheet?

A new worksheet?
 

STEVENS3010

Board Regular
Joined
Feb 4, 2020
Messages
61
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
ok, so I have a report (excel spreadsheet) that is saved daily to a specific folder. I would like to build a macro in a spreadsheet that will copy the raw data from the latest version of this daily report.

So I have a workbook called ‘Open.sheet.xlsm’ this is the book where the Macro will sit and where I would like data pasted to.
When I run the macro, I would like It to go and open the most recent file in The folder where all the reports are saved, I would like it to copy all data from ‘sheet1’ and then to paste the data into the original workbook ‘open.sheet.xlsm’ closing the report also.

I’ve managed to put together the code that goes and opens the most recent report, the bit I’m stuck on now is copying the data into ‘open.sheet.xlsm’ (the book where the macro sits).
I’m not sure how to copy the raw data over? As the file name that it opens will change daily the copy code will need to account for this.

im really struggling can anybody help?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,286
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

This is pretty straightforward but there's still one part missing - where in 'Open.sheet.xlsm' will the data be copied to?

Let's say you want to copy the data to a sheet named 'Report' and put it under the existing data on that sheet.
Code:
Dim wbRecent

    ' other code
 
    myMostRecentFile = myRecentFile

    Set wbRecent = Workbooks.Open(Filename:=myDirectory & myMostRecentFile)
 
    wbRecent.Sheets("Sheet1").Range("A1").CurrentRegion.Copy ThisWorkbook.Sheets("Report").Range("A" & Rows.Count).End(xlUp).Offset(1)
 

STEVENS3010

Board Regular
Joined
Feb 4, 2020
Messages
61
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Thanks for your help. I would like to paste the data into cell A1 on sheet1 in Open.sheet.xlsm

How do I reflect this in the above code?

Cheers
 

STEVENS3010

Board Regular
Joined
Feb 4, 2020
Messages
61
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Thanks for this it has worked great! Is there a code I can add to close the raw data report and to go back to open.sheet?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,286
Office Version
  1. 365
Platform
  1. Windows
This will close the workbook that was opened and copied from.
Code:
wbRecent.Close SaveChanges:=False
 

Watch MrExcel Video

Forum statistics

Threads
1,130,183
Messages
5,640,673
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