Forum Discussion

SteveT63's avatar
SteveT63
New Contributor II
3 years ago

Adding a cube vew name to an excel tab

Hi,

This is probably not something I can do. But I was wondering if it's possible to automatically lable a tab in excel witht he name of the cube view I download? It would be a great time saver when I'm dowloading multiple cube vews or creating a book. I haven't been able to find anuthing in the Academy site on that.

 

Thanks in advance

Cubes
  • tomdarnall's avatar
    tomdarnall
    New Contributor III
    3 years ago

    Hi Steve,

     

    If you don't mind running a macro, this one seems to do the trick...

    A couple of things to note...

    • This will only work with one cube view per worksheet
    • When cube views are added to a worksheet, a series of range names are added.  I've found sometimes these start with an underscore, sometimes they don't, so I've tried to account for both cases
    • Right below the line i = i + 1, there are two lines with If conditions.  If you only want worksheets for cube views updated, you can leave it as is.  If you would also like the same functionality for quick views, you can comment out the first If and uncomment out the second one.

    Good luck!

     

    Sub CubeViewSheetNames()
    Dim x As Integer
    Dim sCubeView, WktName As String
    Dim Ws As Worksheet

    For Each Nm In ActiveWorkbook.Names
    i = i + 1
    If Left(Nm.Name, 9) <> "QuickView" And Right(Nm.Name, 10) = "_UpperLeft" Then
    'If Right(Nm.Name, 10) = "_UpperLeft" Then
    x = InStr(Nm.Name, "_UpperLeft")

    If Left(Nm.Name, 1) = "_" Then
    sCubeView = Mid(Nm.Name, 2, x - 2)
    Else
    sCubeView = Mid(Nm.Name, 1, x - 1)
    End If

    WktName = Range(Nm).Parent.Name

    Set Ws = Worksheets(WktName)

    Ws.Name = sCubeView

    End If
    Next Nm
    End Sub