Setting Workbook Variable with Part of Name in Cell

[mjohnston0209]

I have a workbook that I update every week. Part of the name is based on the date and therefore changes each week (i.e. Monthly Revenue (07-10-22).xlsx then Monthly Revenue (07-17-22).xlsx). The date is written as text in Cell U4 of the workbook.

My goal is to declare a workbook variable and then set the name of the workbook. I am having difficulty setting the workbook name because it always changes. I know I can set a workbook name by stating ActiveWorkbook, but I want set the name by a specific name in case I am not in the activeworkbook when I first start running the macro.

I currently use this as my coding.

Dim wb As Workbook
Set wb = Workbooks("Monthly Revenue (" & Cells(4, 21) & ").xlsx")
wb.Activate

However, the macro does not work if the workbook is not active when I run it.

Any help would be greatly appreciated!

 

[Anthony47]

Try this version:

Dim wb As Workbook
On Error Resume Next
    Set wb = Workbooks("Monthly Revenue (" & Cells(4, 21) & ").xlsx")
On Error GoTo 0
If wb Is Nothing Then
    Set wb = Workbooks.Open("Monthly Revenue (" & Cells(4, 21) & ").xlsx")
End If
wb.Activate

 

[mjohnston0209]

Try this version:

Dim wb As Workbook
On Error Resume Next
    Set wb = Workbooks("Monthly Revenue (" & Cells(4, 21) & ").xlsx")
On Error GoTo 0
If wb Is Nothing Then
    Set wb = Workbooks.Open("Monthly Revenue (" & Cells(4, 21) & ").xlsx")
End If
wb.Activate

Sorry for the delayed response.

When I enter that code, I receive the following error message:

Run-time error '1004':

Sorry, we couldn't find Monthly Revenue ().xlsx. Is it possible it was moved, renamed or deleted?

I think it is unable to pull the value from Cell U4 because it can't identify the workbook (which includes the date as part of the title) that contains cell U4.

 

[Anthony47]

To avoid misunderstandig:
-do you mean cell U4 of (a) the active worksheet of the activeworkbook, or U4 of (b) the unknown workbook?
-cell U4 (wherever is located) is a date (if you format it as Number you will se something like 44774, for Aug-1st) or a string (if you format as said it stay unchanged)?
-the path of the new workbook is the same of the active workbook? Or which is?
-is the new workboog ((i.e. Monthly Revenue (08-01-22).xlsx) already open or you need opening it first and the do other operations?