So you will need to align the first slicer, and the macro will do the rest.
Sub AutoArrangeSlicers()
' Places the slicers on the current sheet side by side,
' aligned right next to the upper left first slicer
Dim objSlicerCache As SlicerCache
Dim objSlicer As Slicer
Dim objSlicerMostLeft As Slicer
Dim lFirstTopPosition As Long
Dim lFirstLeftPosition As Long
Dim lFirstWidth As Long
Dim lNewLeft As Long
Dim lGapWidth As Long
Dim lNewSlicerWidth As Long
lGapWidth = -1 ' set the gap width between the slicers
lNewSlicerWidth = 0 ' set to a size > 0 to set the same width to all slicers
' set to 0 to keep the original width of the slicers
For Each objSlicerCache In ActiveWorkbook.SlicerCaches
For Each objSlicer In objSlicerCache.Slicers
If objSlicer.Shape.TopLeftCell.Worksheet.Name = ActiveSheet.Name Then
If lNewSlicerWidth > 0 Then
' set the new same width to all slicers
objSlicer.Width = lNewSlicerWidth
End If
If objSlicerMostLeft Is Nothing Then
Set objSlicerMostLeft = objSlicer
lFirstTopPosition = objSlicer.Top
lFirstLeftPosition = objSlicer.Left
lFirstWidth = objSlicer.Width
Else
' verify
If lFirstLeftPosition > objSlicer.Left Then
' we've got a new one to the left, update info
Set objSlicerMostLeft = objSlicer
lFirstTopPosition = objSlicer.Top
lFirstLeftPosition = objSlicer.Left
lFirstWidth = objSlicer.Width
Else
' skip
End If
End If
End If
Next objSlicer
Next objSlicerCache
' Okay, we've got the most left position.
' Now, loop through all slicers again and position them right next to the first one
' with a small gap
lNewLeft = lFirstLeftPosition + lFirstWidth + lGapWidth
For Each objSlicerCache In ActiveWorkbook.SlicerCaches
For Each objSlicer In objSlicerCache.Slicers
If objSlicer.Shape.TopLeftCell.Worksheet.Name = ActiveSheet.Name Then
If objSlicer.Name = objSlicerMostLeft.Name Then
' skip
Else
' process
objSlicer.Top = lFirstTopPosition
objSlicer.Left = lNewLeft
lNewLeft = objSlicer.Left + objSlicer.Width + lGapWidth
End If
End If
Next objSlicer
Next objSlicerCache
End Sub
Bookmarks