Using a variable for worksheet name

[BeechamC]

Hi all - first, let me say this is an enormously helpful forum! For weeks I've found every answer I needed without even registering. I will be sure to contribute back as/when I'm able!

I have an issue using a variable as a worksheet name. I have code finding the first name of a user from Application.Username, and then I want to call a worksheet specific to this person. Current code:

Dim UName As String
Dim unameSplit As Variant
Dim UFN As String

UName = Application.UserName

unameSplit = Split(UName, ",")
UFN = unameSplit(1)

Set wstemp = Sheets(UFN)


I currently get error "Subscript out of range" when running. If I change wstemp to Sheets("Peter") it works however. What am I doing wrong?

 

[Andrew Poulsom]

Welcome to MrExcel.

Does this work for you?

Dim UName As String
Dim unameSplit As Variant
Dim UFN As String
UName = Application.UserName
unameSplit = Split(UName, " ")
UFN = unameSplit(0)

 

[BeechamC]

Thanks. The problem doesn't seem to be how UFN is defined/created, because UFN's value in my code is Peter when tested in a label. The problem seems to be in setting the worksheet as the variable UFN rather than a string like "Peter"... flummoxed!

 

[Andrew Poulsom]

Did you actually try the code I posted?

 

[BeechamC]

Yes, I get the exact same "Subscript out of range" error message. Does the worksheet collection only accept limited types of input perhaps?

 

[Andrew Poulsom]

What does this return?

MsgBox Application.UserName

 

[BeechamC]

MsgBox Application.Username returns: Beecham, Peter
MsgBox UFN returns: Peter

 

[Andrew Poulsom]

You need to remove the leading space:

Sub T()
    Dim UName As String
    Dim unameSplit As Variant
    Dim UFN As String
    UName = Application.UserName
    unameSplit = Split(UName, ",")
    UFN = Trim(unameSplit(1))
    Set wstemp = Sheets(UFN)
End Sub

 

[BeechamC]

Works like a dream - thanks so much Andrew!