VBA to Open File (filename contains current date) & copy Sheet to active workbook

Hi,

Place a button anywhere within your workbook (it doesn't have to be the "data" tab if you don't want it to be), and then use this code for the Click event:

Private Sub CommandButton1_Click()
    Dim WB As Workbook
    
    'This will automatically determine the name of the file you want to open
    'If the file resides in a different directory, change "ThisWorkbook.Path"
    'Make sure the file extension is correct at the end of the next line also
    Set WB = Workbooks.Add(ThisWorkbook.Path & "\report " & Month(Date) & "-" & Day(Date) & "-" & Year(Date) & ".xlsx")
    
    'Copy all cells from "Sheet2" in the daily workbook
    WB.Worksheets("Sheet2").Cells.Copy
    
    'Paste the contents into the "data" worksheet in this workbook
    ThisWorkbook.Worksheets("data").Range("A1").PasteSpecial
    
    'Clear the clipboard of the data that you just copied
    Application.CutCopyMode = False
    
    'Close the daily report workbook
    WB.Close
    
    'Select cell "A1" on the "data" worksheet
    ThisWorkbook.Worksheets("data").Range("A1").Select
End Sub

Hope this helps

Don't forget to click the star and mark the thread as solved if I have helped you