Guest User

Untitled

a guest
Jan 21st, 2019
86
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.21 KB | None | 0 0
  1. Sub Step_Thru_SlicerItems2()
  2. Dim slItem As SlicerItem
  3. Dim i As Long
  4.  
  5. ActiveSheet.Shapes.AddShape(msoShapeRectangle, 367.8, 3.6, 159, 54).Select
  6. With Selection.ShapeRange.Fill
  7. .Visible = msoTrue
  8. .ForeColor.RGB = RGB(255, 255, 255)
  9. .Transparency = 0
  10. .Solid
  11. End With
  12. Selection.ShapeRange.Line.Visible = msoFalse
  13. Selection.ShapeRange.Name = "WhiteSquare"
  14. Selection.Name = "WhiteSquare"
  15.  
  16. Application.ScreenUpdating = False
  17. With ActiveWorkbook.SlicerCaches("Slicer_Student")
  18. '--deselect all items except the first
  19. .SlicerItems(1).Selected = True
  20. For Each slItem In .VisibleSlicerItems
  21. If slItem.Name <> .SlicerItems(1).Name Then _
  22. slItem.Selected = False
  23. Next slItem
  24. Call MyFunction(1)
  25. '--step through each item and run custom function
  26. For i = 2 To .SlicerItems.Count
  27. .SlicerItems(i).Selected = True
  28. .SlicerItems(i - 1).Selected = False
  29. Call MyFunction(i)
  30. Next i
  31. End With
  32. Application.ScreenUpdating = True
  33.  
  34. ActiveSheet.Shapes.Range(Array("WhiteSquare")).Select
  35. Selection.Delete
  36.  
  37. End Sub
  38.  
  39. Function MyFunction(lItem As Long)
  40. Dim wsPivot As Worksheet
  41. Dim lNextRow As Long
  42. Const lRowsPerPic As Long = 11
  43. lNextRow = (lItem - 1) * lRowsPerPic + 1
  44.  
  45. Sheets("SemReport").PrintOut Copies:=1, Collate:=True, ignorePrintAreas:=False
  46.  
  47.  
  48. End Function
Add Comment
Please, Sign In to add comment