Referencing to dynamic worksheet

AATU87

New Member
Joined
Jul 5, 2019
Messages
4
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?
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

baitmaster

Well-known Member
Joined
Mar 12, 2009
Messages
2,037
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

New Member
Joined
Jul 5, 2019
Messages
4
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

Well-known Member
Joined
Mar 12, 2009
Messages
2,037
Code:
' 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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,100,060
Messages
5,472,213
Members
406,809
Latest member
haf19

This Week's Hot Topics

Top