How to create a dynamic VBA code in Excel so that it always refers to a workbook with a changing name?


I want to create a macro where it will copy some data from one workbook (whose name stays always same - "SameNameWorkbook") and pastes that data in another open workbook whose name is changing everyday (because its name is a date). For example today my workbook which I want to paste the data in is called "11.06.2021". What I did is I created a =today() formula in the J2 cell in the active workbook (different from the other 2 and named "CurrentWorkbook") and created a variable in VBA for the workbook with changing name:

        `Second_workbook = Range("J2").Value`

When I want to have always a reference to the second workbook I wrote this:


Since Second_workbook is a variable linked to the =today() formula which is 11.06.2021 I thought that will put the date before .xlsx. However, this shows an error so my logic is wrong. Since I am more fond of Excel formulas I thought that this logic will work like the indirect function but obviously it doesn't.

So the end result which I want to have is following:


Tomorrow, then I want to have the following:


... and so on without me manually changing the name workbook in the macro everyday while I keep all 3 workbooks open of course.

Could you please help me with this issue? I would really appreciate your help.


Date Formatting

  • You have to format your date:

    Format(Date, "") & ".xlsx"
    Format(Range("J2").Value, "") & ".xlsx"

    Date is the equivalent of Excel's TODAY in VBA.

  • Here's a common scenario (adjust the worksheet names and the ranges):

Option Explicit

Sub CopyToToday()
    Dim swb As Workbook: Set swb = ThisWorkbook ' workbook containing this code
    ' Attempt to create a reference to the Destination Workbook.
    Dim dName As String: dName = Format(Date, "") & ".xlsx"
    On Error Resume Next
    Dim dwb As Workbook: Set dwb = Workbooks(dName)
    On Error GoTo 0
    If dwb Is Nothing Then
        MsgBox "Could not create a reference to today's workbook.", _
            vbCritical, "Workbook not open"
        Exit Sub
    End If
    ' Copy a range from Source Worksheet to Destination Worksheet.
    Application.CutCopyMode = False
End Sub

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at


Login to comment


TOP Ranking