Macro to Format Subtotals - Guidance Requested!

sabbathstevie

New Member
Joined
Mar 17, 2010
Messages
8
Hi everyone,

I'm using the "GoTo-Special-Visible cells only" method of formatting my subtotals, so that they appear different from the data in the table. This is embedded in a macro which attempts to format the subtotals immediately after the subtotals have been added (to a range which is imaginatively titled "subtotal"), so they are still selected.

I first format the subtotals as I wish (category 2) before then formatting the grand total (category 1) slightly differently. I have the column headers named as the range "title", so they can be formatted appropriately at the end because, since my macro begins to format the subtotals with the headers selected, they are included in the formatting process and should not be.

The problem I have is that while most of my subtotals are formatting correctly, the very last subtotal and the grand total are not.

Here is the code:

Code:
 Application.Goto Reference:="subtotal"
    Selection.Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(5, 7, 8, 9) _
        , Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    ActiveSheet.Outline.ShowLevels RowLevels:=2
    Selection.SpecialCells(xlCellTypeVisible).Select
    With Selection.Font
        .Name = "Tahoma"
        .FontStyle = "Bold"
        .Size = 14
        .Strikethrough = False
        .Superscript = True
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Application.Goto Reference:="title"
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    ActiveSheet.Outline.ShowLevels RowLevels:=3
Can anyone spot where I've gone wrong? Any help will be gratefully appreciated.

Regards,
Stevie
 

Forum statistics

Threads
1,084,856
Messages
5,380,319
Members
401,664
Latest member
traveler84

Some videos you may like

This Week's Hot Topics

Top