CODEWITHSUNDEEP
vbaexcelloops
dmcknight
dmcknight

Reputation: 21

Using a an excel vba loop to grab a cell value and use in a Slicer

I am trying to use vba to run a slicer which I have managed to do by recording a macro. I am now trying to run the slicer multiple times based on cell values from B2 to B13. Each time the Slicer runs based on one cell value, I want to save the excel file and loop until all Slicer options have been run.

Here is the code;

Sub sliceandsend_rwanda()


'This defines the range of offices to run in the slicer
Dim ws1 As Worksheet
Dim sliceoff As Range
Set ws1 = ThisWorkbook.Sheets("Office Codes")
Set sliceoff = Range("B2:B13")



'This defines the file path and naming structure
Dim Name As String
Dim Month As String
Dim Folder As String

Name = "name"
Month = Format(CStr(Now), "(mmm yyyy) - ")
Folder = "location"

Workbooks("name.xlsx").Activate
Dim ws2 As Worksheet
Dim SliceName As Range
Set ws2 = ActiveWorkbook.Sheets("Select")
Set SliceName = Range("C30")

    'ActiveWorkbook.SlicerCaches("Slicer_Organisation_Hierarchy"). _
    'VisibleSlicerItemsList = Array( _
    '"[Organisations].[Organisation Hierarchy].[Dept - Office].&[1009]")

    'Workbooks("Africa Dept-Office Dashboard.xlsx").Activate
    'ActiveWorkbook.SaveAs Filename:=Folder & Name & Month & SliceName

Dim ws3 As Worksheet
Set ws3 = ThisWorkbook.Sheets("Office Codes")
Dim offRng As Range, cl As Range
Set offRng = Range("B2:B13")
Dim sTo As String

For Each cl In offRng

sTo = sTo & ";" & cl.Value
   Next cl


     ActiveWorkbook.SlicerCaches("Slicer_Organisation_Hierarchy"). _
    VisibleSlicerItemsList = _
    Array("[Organisations].[Organisation Hierarchy].[Dept - Office].&["&   cl.Value & "]")


    Workbooks("name.xlsx").Activate

Upvotes: 1

Views: 428

Answers (1)

dmcknight
dmcknight

Reputation: 21

I got it to work;

Sub sliceandsend_rwanda()

'This defines the range of offices to run in the slicer
Dim ws1 As Worksheet
Dim sliceoff As Range
Set ws1 = ThisWorkbook.Sheets("name")
Set sliceoff = Range("B2:B13")

'This defines the file path and naming structure
Dim Name As String
Dim Month As String
Dim Folder As String

    Name = "name"
    Month = Format(CStr(Now), "(mmm yyyy) - ")
    Folder = "link"

Workbooks("name").Activate
Dim ws2 As Worksheet
Dim SliceName As Range
    Set ws2 = ActiveWorkbook.Sheets("name")
    Set SliceName = Range("C30")


Workbooks("name").Activate
    'ActiveWorkbook.SaveAs Filename:=Folder & Name & Month & SliceName

Dim ws3 As Worksheet
    Set ws3 = ThisWorkbook.Sheets("name")
Dim offRng As Range, cl As Range
    Set offRng = ThisWorkbook.Worksheets("name").Range("B2:B13")
Dim sTo As String

For Each cl In offRng
    sTo = sTo & cl.Value

    ActiveWorkbook.SlicerCaches("Slicer_Organisation_Hierarchy"). _
    VisibleSlicerItemsList = ("[Organisations].[Organisation Hierarchy].[Dept - Office].&[" & sTo & "]")

Next cl

End Sub

Upvotes: 1

Related Questions