Hi Guys,

I am working in a workbook (let's call it "Master workbook"). When I run a macro other workbooks are opened and data is collated from them and pasted into the Master Workbook. One of the workbooks that the macro opens is a workbook that is replaced daily. The title of the workbook is a date e.g. OtherWorkbook 01-06-2016.xlsx Because the name of the file changes the macro uses a date in C10 and data in cell D10 of my master workbook to enable me to open the file.

Cell C10 has the date 01/06/2016 and cell D10 has the formula ="OtherWorkBook "&TEXT(C10,"dd-mm-yyyy")&".xlsx" resulting in D10 containing OtherWorkbook 01-06-2016.xlsx

When in my Master Workbook the following code opens OtherWorkbook 01-06-2016.xlsx

Application.Workbooks.Open ("C:\My\File\Path\" & Range("D10")) 

    Sheets("Sheet 1").Select
    Cells.Select
    Selection.Copy
    Windows("Master Workbook.xlsm").Activate
    Sheets("Sheet 1").Select
    Cells.Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
Once the information from OtherWorkbook 01-06-2016.xlsx has been copied into my Mater Workbook I want some code to activate OtherWorkbook 01-06-2016.xlsx and close the workbook. How do I write this as VBA code when the name of the file is a date that changes i.e. the name of the file will not always be the same.

Any help you can give me is much appreciated.

Thanks