Personal WB Macro- Dynamically name the "other" file

[Remotruker]

When i open an excel file, two files actually open- the file I clicked on and the hidden Personal workbook. I have a macro saved in my Personal workbook so i can use it across many different files, but in order to do so, i need a way to name the file i actually clicked on, which will be dynamic.

When i use ThisWorkbook.name it references the Personal, not the one I opened.

Ideas???

[HaHoBe]

Hi, Remotruker,

maybe like this:

Sub TestForOtherWorkbook()
Dim wb As Workbook
Dim wbOther As Workbook

If Workbooks.Count > 1 Then
  For Each wb In Workbooks
    If wb.Name <> "PERSONALÖ.XLSB" Then
      Set wbOther = wb
      Exit For
    End If
  Next wb
End If

If Not wbOther Is Nothing Then
  MsgBox "Name of the other workbook is: " & wbOther.Name
End If

Set wbOther = Nothing

End Sub

Or even simpler as the personal workbook should be insvisible: use ActiveWorkbook instead of ThisWorkbook (which holds the code).

Ciao,
Holger

[Remotruker]

Holger,
This works!
Thanks!

Hi, Remotruker,

maybe like this:

Sub TestForOtherWorkbook()
Dim wb As Workbook
Dim wbOther As Workbook

If Workbooks.Count > 1 Then
  For Each wb In Workbooks
    If wb.Name <> "PERSONALÖ.XLSB" Then
      Set wbOther = wb
      Exit For
    End If
  Next wb
End If

If Not wbOther Is Nothing Then
  MsgBox "Name of the other workbook is: " & wbOther.Name
End If

Set wbOther = Nothing

End Sub

Or even simpler as the personal workbook should be insvisible: use ActiveWorkbook instead of ThisWorkbook (which holds the code).

Ciao,
Holger