Hi,
I have a VBA question,
I'm trying to ensure the formatting of a pivot table (Excel 2003) by using a refresh VBA code activated by the user.
I have a code which can do this but it needs to select the sheet where the pivot table is.
the code I currently have is:
as you can see it uses "Pivotselect" and then "Selection" which means as in my code above the sheet2 needs to be selected.
if I want to copy a cell I might write sheet2.cells(1,1).copy rather than select then selection.copy, is there a similar change I can do to my code above (pressumably subsituting PivotSelect for something else?) so that I can run is successfully from any sheet without it needing to select sheet 2?
THanks in advance for your help,
Andy
I have a VBA question,
I'm trying to ensure the formatting of a pivot table (Excel 2003) by using a refresh VBA code activated by the user.
I have a code which can do this but it needs to select the sheet where the pivot table is.
the code I currently have is:
Code:
Sheet2.Select
Sheet2.PivotTables("PivotTable3").PivotSelect "'Sum of Variance'", _
xlDataAndLabel, True
Selection.Interior.ColorIndex = 36
Sheet2.PivotTables("PivotTable3").PivotSelect "'Ops/SMC'[All;Total]", _
xlDataAndLabel, True
Selection.Interior.ColorIndex = 47
Sheet2.PivotTables("PivotTable3").PivotSelect "'Column Grand Total'", _
xlDataAndLabel, True
Selection.Interior.ColorIndex = 16
as you can see it uses "Pivotselect" and then "Selection" which means as in my code above the sheet2 needs to be selected.
if I want to copy a cell I might write sheet2.cells(1,1).copy rather than select then selection.copy, is there a similar change I can do to my code above (pressumably subsituting PivotSelect for something else?) so that I can run is successfully from any sheet without it needing to select sheet 2?
THanks in advance for your help,
Andy