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

Question

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:

        `Windows("Second_workbook.xlsx").Activate`
        `Range.("A1").Select`
        `ActiveSheet.Paste`

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:

        `Windows("11.06.2021.xlsx").Activate`

Tomorrow, then I want to have the following:

        `Windows(12.06.2021.xlsx").Activate`

... 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.

Try to [Avoid using select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Christofer Weber, Jun 11 '21 at 08:18

Christofer Weber · Answer 1 · 2021-06-11T09:05:29.143

1

"Second_workbook.xlsx" is a string and will be interpreted as a string, ignoring any variables with the same name.

Variables are written out without quotes, and strings of text have the quotes. Everything within quotes (green text) is taken as a string of text. To combine strings and variables we use the & operand like so:

"string" & variable & "string"

So what you are looking for should be:

Windows(Second_workbook & ".xlsx").Activate

You might want to save the workbook as a variable object instead, to refer to it easier:

Dim wb As Workbook
Set wb = Workbooks(Range("J2") & ".xlsx")

Or if you are using the Second_workbook variable anyway, you can set it like:

Set wb = Workbooks(Second_workbook & ".xlsx")

Remember that this range, just as in your example will be interpreted as ActiveWorkbook.ActiveSheet.Range("J").value unless you specify it. Make sure that this won't cuase problems.

To activate a cell in this workbook, you can use wb.Worksheets(1).Range("A1").Select, for example.

edited Jun 11 '21 at 09:05

answered Jun 11 '21 at 08:16

Christofer Weber

1,464
1
9
18

Thank you but when I run it still gives me error on this row `Set wb = Workbooks(second_workbook & ".xlsx")` The code for the workbook reference would be like this right?: `Windows(wb).Activate` – MilanovI Jun 11 '21 at 09:05
What error are you getting? I rearranged some of it for clarity. To activate the workbook you can just use `wb.Activate` once you have `wb` set as a workbook object variable. – Christofer Weber Jun 11 '21 at 09:10
It shows Run-time error "9": Subscript out of range – MilanovI Jun 11 '21 at 09:44
Yeah, then the answer is in the other answer. Your date formatting isn't the same as the name of the workbook, so when trying to set the workbook, excel can't find it. you can use `debug.print Second_workbook` or `MsgBox Second_workbook` to double-check this. – Christofer Weber Jun 11 '21 at 09:47
Thanks, after I formatted the date it worked. – MilanovI Jun 11 '21 at 14:50

VBasic2008 · Accepted Answer · 2021-06-11T09:16:57.520

Date Formatting

You have to format your date:

Format(Date, "dd.mm.yyyy") & ".xlsx"
Format(Range("J2").Value, "dd.mm.yyyy") & ".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, "dd.mm.yyyy") & ".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.
    swb.Worksheets("Sheet1").Range("A1:F10").Copy
    dwb.Worksheets("Sheet1").Range("A1").PasteSpecial
    Application.CutCopyMode = False
    'dwb.Save
    
End Sub

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

2 Answers2

Date Formatting