CODEWITHSUNDEEP
vbaexcel
dGottwik
dGottwik

Reputation: 3

How do I return the name of sheet with a variable on it in a cell?

I am trying to have a cell on sheet 1 return the name of the sheet the variables 'r1' (on sheet2) or 'r2' (on sheet3) are referenced from, if they meet qualifications. What I currently have is:

Public Function whichsheet(r1 As Range, r2 As Range) As String


If Application.Sum(r1) > 0 Then
    whichsheet = whichsheet & Application.Caller.Worksheet.Name

End If

If Application.Sum(r2) > 0 Then
    whichsheet = whichsheet & Application.Caller.Worksheet.Name

End If

End Function

Where Application.Caller.Worksheet.Name is currently a placeholder that pulls the name of the first sheet, "sheet1" rather than "sheet1" and/or "sheet2".

Upvotes: 0

Views: 77

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

The following will return the sheet name of the ranges as they are passed.

Public Function whichsheet(r1 As Range, r2 As Range) As String


If Application.Sum(r1) > 0 Then
    whichsheet = whichsheet & r1.Parent.Name

End If

If Application.Sum(r2) > 0 Then
    whichsheet = whichsheet & r2.Parent.Name

End If

End Function

Upvotes: 4

Related Questions