0

My code that formats certain rows on my pivot table works when I step through the code, step by step. However, when I assigned the macro to a button, with other macros, it didn't complete the formatting in the finished product. As a result, when I copied the pivot table data across to a new sheet, the correct formatting wasn't present.

Basically I am trying to format certain rows in the pivot table, and then copy the values and format of the pivot table to a new sheet and format the rest there (as formatting the pivot table is slowing down my macro considerably).

Here is the start of my code:

Sub formatpt()
 'formatpt Macro

Dim ws As Worksheet
Set ws = Worksheets("Pivot")
Dim pt As PivotTable
Set pt = Worksheets("Pivot").PivotTables("Dashboard")

pt.ShowTableStyleRowHeaders = False
pt.ShowDrillIndicators = False

pt.PivotSelect "Region[All]", xlLabelOnly + _
    xlFirstRow, True
Selection.Interior.Color = RGB(128, 128, 128)
Selection.RowHeight = 40
Selection.Font.Bold = True
Selection.Font.Color = vbWhite

pt.PivotSelect "Desk[All]", xlLabelOnly + _
    xlFirstRow, True
Selection.Interior.Color = RGB(166, 166, 166)
Selection.RowHeight = 35
Selection.Font.Bold = True

pt.PivotSelect "Style[All]", xlLabelOnly + _
    xlFirstRow, True
Selection.Interior.Color = RGB(217, 217, 217)
Selection.RowHeight = 20
Selection.Font.Bold = True

Set ws2 = Worksheets("Final")

ws.Range("A1").CurrentRegion.Copy
ws2.Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone _
    , SkipBlanks:=False, Transpose:=False
ws.Range("A1").CurrentRegion.Copy
ws2.Range("A1").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

The row heights don't copy across either, but this is less important. But if you do know how to copy across row height as well as the formatting and values, then please let me know.

Any help is greatly appreciated.

Thanks

Mesut Akcan
  • 899
  • 7
  • 19
H Smith
  • 1
  • 1
  • 1
    Is anything selected when you hit the button? I'd give [How to avoid using Select in Excel VBA](https://stackoverflow.com/q/10714251/4088852) a read. – Comintern Nov 22 '18 at 16:43
  • Apart from the ".PivotSelect" syntax, no. I saw that article posted before and took it into consideration as a result. But I don't know how to avoid PivotSelect. Interestingly I just tested the macro in a new button, with just the "formatpt" macro assigned, and it worked? @Comintern – H Smith Nov 22 '18 at 16:48
  • Managed to solve it. Unfortunately had to just activate the sheet before the code. Not ideal but it works. – H Smith Nov 22 '18 at 17:42

0 Answers0