Referencing to dynamic worksheet

[AATU87]

Hi everyone,

I'm trying to write a code which refers a dynamic worksheet. I have daily file whose name changes based on the date. So far, I have tried the code below:

Dim TodayDate As String
TodayDaye= Now

Windows("Now" & Daily Inventory Followup.xlsx").Activate


However, this is not working; I only get an error message.
COuld someone please help me to solve the problem?

 

[baitmaster]

Various things wrong here, and all fixable

- You're getting confused about your object type - I assume we're talking about a workbook, not a worksheet?

- What is the date format in the Workbook file name? Please give me some examples of file names

- Will the file always be open before running this code? Or do we need to open it if it's not already open - if so, from what folder path?

- Finally, what are you actually trying to do? This code is to simply activate an open file, why are you doing this? Just to activate it on screen for the User? Or because you want to do something with it and return to this file afterwards? If the latter then we may not want to actually activate it, we might create a workbook object instead and process it without ever activating or selecting it

 

[AATU87]

Various things wrong here, and all fixable

- You're getting confused about your object type - I assume we're talking about a workbook, not a worksheet?

Yes indeed, we are talking about a workbook.

- What is the date format in the Workbook file name? Please give me some examples of file names

The date format is YYYYMMDD.
Examples of file names :
20190703 Daily Inventory Followup
20190704 Daily Inventory Followup
20190704 Daily Inventory Followup
20190705 Daily Inventory Followup

- Will the file always be open before running this code? Or do we need to open it if it's not already open - if so, from what folder path?

The file will always be open when running the code. No need to open it.

- Finally, what are you actually trying to do? This code is to simply activate an open file, why are you doing this? Just to activate it on screen for the User? Or because you want to do something with it and return to this file afterwards? If the latter then we may not want to actually activate it, we might create a workbook object instead and process it without ever activating or selecting it

I want to activate the file in order to copy past data from another file.



Thank you for your kind help!

 

[baitmaster]

' create filename that we're looking for
Dim strWorkbookName As String: strWorkbookName = Format(Date, "yyyymmdd") & " Daily Inventory Followup.xlsx"


' attempt to create workbook object. If it doesn't exist, [Nothing] will be returned
Dim wb As Workbook
On Error Resume Next        ' in case workbook with this name is not open
    Set wb = Workbooks(strWorkbookName)
On Error GoTo 0            ' turn error handling off when finished


' test if workbook was not found and report error if necessary
If wb Is Nothing Then
    MsgBox "ERROR: unable to find workbook:" & vbLf & strWorkbookName
    Exit Sub
End If


' by this point, a workbook object has been created and can be used
wb.Activate