En VBA hay tres referencias comunes que te pueden ayudar a navegar por el contenido de un libro de trabajo y son extremadamente simples. Aquí las encapsulé en tres funciones, pero puedes tomar las líneas según lo necesites. Ya sea que necesites el nombre del archivo, el nombre completo (con ruta) o el nombre de la hoja.
Function elNombreDeEsteArchivo() As String
Application.Volatile
elNombreDeEsteArchivo = ThisWorkbook.Name
End Function
Function elNombreCompletoDeEsteArchivo() As String
Application.Volatile
elNombreCompletoDeEsteArchivo = ThisWorkbook.FullName
End Function
Function elNombreDeLaHoja()
Application.Volatile
elNombreDeLaHoja = ActiveSheet.Name
End Function
Descarga el archivo: VBAreferences