Declare WB as public where file name is based on cell value

Question

I need help on declaring 2 workbooks globally where the workbook name is based on a cell value.

I have 3 workbooks workbook

1. "UPSHI" (file name does not change) workbook
2. "UAH DR_M04 2022"(M04 and 2022 changes monthly depending in month & year) workbook
3. "2022_M04_OP21_AAH"(2022, M04 & OP21 changes monthly).

UPASHI workbook will be my main file which will create all my buttons and formulas, I created formula in cell B1 for the 2nd workbook that will identify the current name of the 2nd workbook and another formula in cell B7 to identify current name of my 3rd workbook.

I used cell B1 & B7 to activate the workbooks which work fine but I cannot make it work when i want to copy a sheet from workbook 2 to my workbook 3.

I wanted to declare a global variable for each workbook that I can just call when I want to activate or select the work book, how can I make it work?.

Here is my current code:

Sub PrepFile_Click()
    Workbooks(ActiveSheet.Range("b1").Value).Activate
    Application.DisplayAlerts = False
    Sheets("Upstream Asset Hierarchy -OLD").Delete
    Application.DisplayAlerts = True
    Sheets("Upstream Asset Hierarchy Viewer").Name = "Upstream Asset Hierarchy -OLD"
            
    Workbooks("UPASHI Automation File").Activate
    Workbooks(ActiveSheet.Range("b7").Value).Activate
    Sheets("Upstream Asset Hierarchy Viewer").Copy before:=Workbooks(ActiveSheet.Range("b1").Value).Sheets("UPSASSET")
End Sub

Answer 1

Try the next way, please:

1. Declare four Public variables on top of a standard module (in the declarations area):

 Option Explicit

 Public wbUPS as Workbook, wbUAH as Workbook, wb3 As Workbook, ws As Worksheet

2. Create a Sub, let us say "SetWorbooks", placing the next code:

Sub SetWorbooks
   Set wbUPS = ThisWorkbook
    Set ws = wbUPS.Worksheets("The name of the active sheet")
   Set wbUAH = Workbooks(ws.Range("b1").Value)
   Set wb3 = Workbooks(ws.Range("b7").Value)  
End Sub

Run the above code (once) and then just using wbUPS, wbUAH, wb3. Of course, the involved workbooks must be open...

3. Use the above variables in the next way:

Sub PrepFile_Click()
    If wbUAH Is Nothing Then SetWorbooks 'just in case
    
    Application.DisplayAlerts = False
     wbUAH.Sheets("Upstream Asset Hierarchy -OLD").Delete
    Application.DisplayAlerts = True
    wbUAH.Sheets("Upstream Asset Hierarchy Viewer").Name = "Upstream Asset Hierarchy -OLD"
            
    wb3.Sheets("Upstream Asset Hierarchy Viewer").Copy before:=wbUAH.Sheets("UPSASSET")
End Sub

Activation, selection do not bring any benefit (if you know how to avoid that), only consume Excel resources...

If something unclear, please do not hesitate to ask for clarifications. Of course, I couldn't test the above solution.