CODEWITHSUNDEEP
excelvba
Moza
Moza

Reputation: 27

Naming a sheet with space in a VLookup function (VBA)

I am trying to get certain information from a sheet and copying it to a different worksheet and my worksheets have long names (cannot change them for a reason)

I keep coming up with message "Compile Error: Expected List Separator or )" and I am pretty sure its to do with the sheet name unless I'm wrong?

Code:

Sub SALEXAMPLE()  
Dim names As String  
names = "Justin Jones"  
Sal = Application.WorksheetFunction.VLookup(names, **salary sheets**.Range("A1:C10"), 2, False)  
MsgBox "Salary is : $ " & Sal
End Sub

Upvotes: 1

Views: 1385

Answers (1)

R3uK
R3uK

Reputation: 14537

You'll have to use Sheets to reference the sheet you want :
ThisWorkbook.Sheets("salary sheets").Range(...

Sub SALEXAMPLE()  
Dim names As String  
names = "Justin Jones"  
Sal = Application.WorksheetFunction.VLookup(names, ThisWorkbook.Sheets("salary sheets").Range("A1:C10"), 2, False)  
MsgBox "Salary is : $ " & Sal
End Sub

Upvotes: 1

Related Questions