Using Environ("USERNAME") in Workbook name

[smpatty08]

I have a workbook that multiple people have their own copy of that has their Windows Username at the end of the file name (e.x. Test jdoe). Right now any time somebody new needs to use the workbook I have to manually update a few lines of code with their username. Here is an example.

Dim a As Worksheet, WinUser As String

Application.ScreenUpdating = False


WinUser = Environ("USERNAME") ' Windows username

Set a = Workbooks("Test " & WinUser & ".xlsm").Sheets("Test")

a.Activate

When I run this I get Run-time error "9": Subscript out of range. I wondering what I am doing incorrectly. Any help would be appreciated

Thanks!

 

[MARK858]

What do you get with

Set a = Workbooks("Test/" & WinUser & ".xlsm").Sheets("Test")

 

[smpatty08]

What do you get with

Set a = Workbooks("Test/" & WinUser & ".xlsm").Sheets("Test")

I still get the same error. There is a space between Test and the Username. So I also tried

Set a = Workbooks("Test /" & WinUser & ".xlsm").Sheets("Test")

and

Set a = Workbooks("Test/ " & WinUser & ".xlsm").Sheets("Test")

Both to no avail