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

MilanovI

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.

VBasic2008

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

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

TOP Ranking

HotTag

Archive