Link to home
Create AccountLog in
Avatar of Svgmassive
Svgmassive

asked on

Used A named ranged to get a sheet name in another workbook

I am using the function below using a named range to get the sheet name in a closed workbook.Is there a better way.


Function GetSheetsNames(file, RAN_GE As String) As String: Dim wb As Object: Dim wsht As Excel.Worksheet
    On Error Resume Next
 Set wb = GetObject(file): Set wsht = wb.Worksheets(wb.Names(RAN_GE).RefersToRange.Parent.name): GetSheetsNames = wsht.name: wb.Close False: Set wb = Nothing
End Function
ASKER CERTIFIED SOLUTION
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
I’m glad I was able to help.

If you expand the “Full Biography” section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange Most Valuable Expert (MVE) 2015, 2017
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2018
              Experts Exchange Top Expert VBA 2018