Search code examples
excelvbaformattingpivot-tablecell

EXCEL: Format cells based on style of adjacent Pivot Table

I'm trying to develop a report which is largely Pivot Table but with some adjacent cells for some other (non pivot-table-able) text values.

Each week the Pivot Table will be refreshed and rows with subheadings etc will move depending on the underlying data.

Every time this happens I have to manually format those adjacent cells to match the format of the pivot table.

Does anyone know how to make the cells inherit the Pivot Table's formatting 'style', please?

Here's an illustration of what I'm trying to achieve: enter image description here

Thanks in advance!

Solution

  • Try something like this, in the Workbook module:

    Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
    Dim c As Range
    With Target.DataBodyRange
        Set c = .Columns(.Columns.Count)
    End With
    With c.End(xlToRight)
        .CurrentRegion.ClearFormats
        c.Copy
        .PasteSpecial Paste:=xlPasteFormats
        Application.CutCopyMode = False
    End With
End Sub