VBA - Windows activate (Name discrepancy)

[MartinST]

I have a workbook named outstanding actions. I have several new workbooks that is created from time to time that I use a macro in these new workbooks to copy the action from these workbooks to the Outstanding actions workbook. The problem is that the workbook name change creates a problem in VBA whereby I don’t know how to overcome the problem on how to change the workbook name in VBA. For Example this workbook is named "Spares" and the Workbook selection will be " Windows("Spares.xls").Activate " in VBA what if the Workbook name changes to "Cars". How can this problem be overcome.
VBA must automatically change the name in the code. I have a cell that contains the workbook name, I am sure it could be used but how do you code that?

 

[Richard Schollar]

Hi

There are a variety of ways you can refer to a workbook - specifically, if the workbook is the one in which the code resides (in a module) you can refer to it via ThisWorkbook:

ThisWorkbook.Activate

Given that you have the name of the workbook in a cell, you can utilise this thus:

Workbooks(Workbooks("SomeWorkbook.xls").Worksheets("SomeSheet").Range("A1").Value).Activate

WhereSomeWorkbook.xls is the one containing the sheet which holds the workbook name in A1 that you want to activate

 

[Sektor]

First, create hidden name with the name of the workbook which will be changed.

Sub CreateHiddenName()

    Dim s As String
    s = "Spares"
    Names.Add Name:="Book", RefersTo:=s, Visible:=False

End Sub

Then you can change it anytime you want.

Sub UseNameInWorkbooks()

    ' Refer to workbook "Spares"
    Workbooks(Names("Book").Value).Activate
    
    ' Change name.
    Names("Book").Value = "Cars"
    
    ' Refer to workbook "Cars"
    Workbooks(Names("Book").Value).Activate
    
End Sub

 

[MartinST]

Thanks Richard,
The "ThisWorkbook.Activate" does the trick!