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