Catching those slicer selections: A macro to do the work for you...

I've been working with PowerPivot for some time now and one thing that I've noticed on each of my solutions that I create is that I spend too much time creating the hidden sheet that "catches" each of the slicer selections that are active.  This can sometimes take hours if the project is big enough.

So, this last time, I decided to take 20 minutes out of my day do a little research on the Google and write a macro that would do exactly that for me.  Too much time is now almost no time at all and I have a bit of my work day back.  

Here is what I use. If you have suggestions on how to make it more efficient or just better practice (self-taught here folks!), please share in the comments!

Summary of Macro: 

Create a worksheet called "HiddenFilterCatch".  

Add Cubeset and CubeRankedMember formulas to new sheet (Prompting user for max number of options per slicer to catch)

Please excuse the blog formatting as I use a bit more screen real estate when coding.  Enjoy!

*Disclaimer - this worked for me in my specific scenario. You may/may not need to adjust the code to fit your situation.  :)

Option Explicit

Sub MultiplePivotSlicerCaches()

    Dim oSlicer As Slicer

    Dim oSlicercache As SlicerCache

    Dim oSh As Worksheet

    Dim SlcrName As String

    Dim Col As Integer 'Column Number

    Dim Connect As String, Comma As String, oPrn As String, cPrn As String 'Formula pieces

    Dim i As Integer 'Number of Slicers

    Dim Fmla As String 'Completed Formula

    Dim ws As Worksheet

    Dim x As Integer 'Max # of Filters determined by user

    Dim z As Variant

    Application.ScreenUpdating = False

    Application.Calculation = xlCalculationManual

    

'Add New Worksheet "HiddenFilterCatch"

    z = MsgBox("Do you have a 'HiddenFilterCatch' Worksheet already?", vbYesNo)

    If z = vbNo Then

        Set ws = Sheets.Add(After:=Sheets(Sheets.Count))

        ws.Name = "HiddenFilterCatch"

    End If

'Count Slicers in Workbook (For Trouble Shooting Code Only)

    'i = 0

    'For Each oSlicercache In ThisWorkbook.SlicerCaches

    'i = i + 1

    'Next

    

'Set Variables for "=Cubeset()"

    Col = 3 'Column Number

    Connect = "=Cubeset(" & Chr(34) & "ThisWorkBookDataModel" & Chr(34) & ","

    Comma = ","

    oPrn = "("

    cPrn = ")"

    

       

'Distribute Cubeset Formulas onto spreadsheet

    Sheets("HiddenFilterCatch").Activate

    For Each oSlicercache In ThisWorkbook.SlicerCaches

            SlcrName = oSlicercache.Name

            Fmla = Connect & SlcrName & Comma & Chr(34) & SlcrName & Chr(34) & cPrn

            Cells(9, Col).Formula = Fmla

            Col = Col + 1

    Next

    

'Set Variables for "=CubeRankedMember()"

    Connect = "=IFERROR(CUBERANKEDMEMBER(" & Chr(34) & "ThisWorkBookDataModel" & Chr(34) & ","

    

'Distribute CubeRankedMember Formulas onto spreadsheet

    Col = 3

    x = InputBox(Prompt:="What is the Maximum Number of filters you would like to show?", Title:="Maximum Number of Filters", Default:="4")

    For Each oSlicercache In ThisWorkbook.SlicerCaches

            'Add formulas for CubeRankedMembers 1 to "x"

                For i = 1 To x

                    Fmla = Connect & Cells(9, Col).Value & ",Row(HiddenFilterCatch!A" & i & "))," & Chr(34) & Chr(34) & ")"

                    Cells(9 + i, Col).Formula = Connect & Cells(9, Col).Value & ",Row(HiddenFilterCatch!A" & i & "))," & Chr(34) & Chr(34) & ")"

                Next

            'Add "More than 'x' Selected..." Text formula

                Cells(10 + x, Col).Formula = "=IF(" & Right(Fmla, Len(Fmla) - 1) & "=" & Chr(34) & Chr(34) & "," & Right(Fmla, Len(Fmla) - 1) & "," & Chr(34) & "More Than " & x & " Selected..." & Chr(34) & ")"

            'Add named Range on Cell below "More Than" formula

                Cells(11 + x, Col).Select

                With Selection

                    .Name = Right(Cells(9, Col).Value, Len(Cells(9, Col).Value) - 7)

                End With

                Col = Col + 1

    Next

    Application.ScreenUpdating = True

    Application.Calculation = xlCalculationAutomatic

    

    

End Sub

Using VBA to Reformat a Report

This post is going to be somewhat short and sweet - I'm not going to explain every little part, but I will provide a demo file for you to play with.

The Problem:
A report from a 3rd party vendor can only be exported to Microsoft Word is very pretty and in a table-like format.  However, each individual data point was contained in what appeared to be a textbox. Therefore, when attempting to copy paste the results into excel, all headers, footers, and data points are in their own cell, but in the same column.

Example of pasting report in cell A1:

**Note: I have changed ALL of the data and this report does NOT reflect anything real.

The Solution:

My mind immediately went to VBA for the solution this time.  Here are a few concepts that you need to know about to accomplish this task:

• Option Explicit - always use this to ensure all variables are declared
• Screen Updating - I use this to keep the screen from flashing when switching sheets and to make the macro run faster
• Do until Loop - This is used to step down the worksheet row-by-row until a certain condition is met
• Select Case - This is like a big IF/Then statement. 

When I create a "Formatting" macro, I like to have one spreadsheet for the original "pasted" format, and worksheet for the result after the macro runs.  This allows you to quickly, visually compare original vs new to validate accuracy of your code. 

The Code:

Simplified Explanation:  The macro looks at each populated row looking for the department name.  Once found, it gathers the information for that departments encounter and then places it in a more tabular format on the "Final" worksheet. It will do this until the "LastRow" (assigned by counting the populated rows) is evaluated.

Option Explicit

Sub Reorganize()

Dim row As Variant, lastrow As Variant, Dept As Variant, Isle As Variant, ID As Variant, CartoonAccount As Variant, CartoonName As Variant, Order As Variant, Row1 As Integer

Application.ScreenUpdating = False

Sheets("Paste").Activate

lastrow = ActiveSheet.UsedRange.Rows.Count

row = 1

Do Until row = lastrow

    If row >= lastrow Then Exit Do

    Select Case Cells(row, 1).Value

        Case Is = "Health"

            Dept = Cells(row, 1).Value

            Isle = Cells(row + 1, 1).Value

            ID = Cells(row + 2, 1).Value

            Order = Cells(row + 3, 1).Value

            CartoonName = Cells(row + 4, 1).Value

            CartoonAccount = Cells(row + 5, 1).Value

                

                    Sheets("Final").Activate

                        Row1 = 2

                        Do Until Cells(Row1, 1).Value = ""

                            If Cells(Row1, 1).Value = "" Then Exit Do

                            Row1 = Row1 + 1

                        Loop

                        

                        Sheets("Final").Cells(Row1, 1).Value = Dept

                        Sheets("Final").Cells(Row1, 2).Value = Isle

                        Sheets("Final").Cells(Row1, 3).Value = ID

                        Sheets("Final").Cells(Row1, 4).Value = Order

                        Sheets("Final").Cells(Row1, 5).Value = CartoonName

                        Sheets("Final").Cells(Row1, 6).Value = CartoonAccount

                        Row1 = 2

                        Sheets("Paste").Activate

                        row = row + 1

                                     

        Case Is = "FurnitureDept"

            Dept = Cells(row, 1).Value

            Isle = Cells(row + 1, 1).Value

            ID = Cells(row + 2, 1).Value

            Order = Cells(row + 3, 1).Value

            CartoonName = Cells(row + 4, 1).Value

            CartoonAccount = Cells(row + 5, 1).Value

                

                    Sheets("Final").Activate

                        Row1 = 2

                        Do Until Cells(Row1, 1).Value = ""

                            If Cells(Row1, 1).Value = "" Then Exit Do

                            Row1 = Row1 + 1

                        Loop

                        

                        Sheets("Final").Cells(Row1, 1).Value = Dept

                        Sheets("Final").Cells(Row1, 2).Value = Isle

                        Sheets("Final").Cells(Row1, 3).Value = ID

                        Sheets("Final").Cells(Row1, 4).Value = Order

                        Sheets("Final").Cells(Row1, 5).Value = CartoonName

                        Sheets("Final").Cells(Row1, 6).Value = CartoonAccount

                        Row1 = 2

                        Sheets("Paste").Activate

                        row = row + 1

        Case Is = "Toys"

            Dept = Cells(row, 1).Value

            Isle = Cells(row + 1, 1).Value

            ID = Cells(row + 2, 1).Value

            Order = Cells(row + 3, 1).Value

            CartoonName = Cells(row + 4, 1).Value

            CartoonAccount = Cells(row + 5, 1).Value

                

                    Sheets("Final").Activate

                        Row1 = 2

                        Do Until Cells(Row1, 1).Value = ""

                            If Cells(Row1, 1).Value = "" Then Exit Do

                            Row1 = Row1 + 1

                        Loop

                        

                        Sheets("Final").Cells(Row1, 1).Value = Dept

                        Sheets("Final").Cells(Row1, 2).Value = Isle

                        Sheets("Final").Cells(Row1, 3).Value = ID

                        Sheets("Final").Cells(Row1, 4).Value = Order

                        Sheets("Final").Cells(Row1, 5).Value = CartoonName

                        Sheets("Final").Cells(Row1, 6).Value = CartoonAccount

                        Row1 = 2

                        Sheets("Paste").Activate

                        row = row + 1

        Case Else

            row = row + 1

    End Select

Loop

Application.ScreenUpdating = True

Sheets("Final").Activate

End Sub

I LOVE VBA - What can I say, it was my first programming language.. I'm self-taught so if you are an expert and have a better way to accomplish this, feel free to contribute to the conversation!  Again, we're here to learn together- from each other.  

Demo File (Does Contain Macros!):