Pivot Refresh Macro

[PipBoy808]

I've been using a workbook that contains the following code:

Sub New_Pivot_refresh()
'
' New_Pivot_refresh Macro
'
'
Sheets("1. COGS vs. Production").Select
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("E17").Select
ActiveSheet.PivotTables("PivotTable3").PivotCache.Refresh
Range("H14").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
Range("U24").Select
ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
Range("W22").Select
ActiveSheet.PivotTables("PivotTable4").PivotCache.Refresh
Sheets("Panel").Select
Range("A1").Select
End Sub


As you can see, there are few comments explaining how it all works. Can anyone explain a couple of things to me?

• Activewindow.Scrollcolumn - what is this doing and why are there four different values provided one after another?
• Is there a significance to each of the cell references? (E17, H14, U24, W22, A1)

Thansk very much. I'd be lost without this forum!

 

[Luke M]

ScrollColumn is used to physically scroll the workbook (positive numbers means scroll to the right). In good code, you don't need those at all, as it's just a visual thing, the code will work just fine w/o a care to what the user is looking at. Also, we usually have

Application.ScreenUpdating = False

at the beginning and

Application.ScreenUpdating = True

at the end to make sure the screen is not flashing with all the things going on, further making the ScrollColumn a little useless. Extra useless having more than 1 in a row ...if you're going to scroll, do it all in 1 shot:

ActiveWindow.ScrollColumn = 10

This code is most often seen when someone used the macro recorder.

No, the Range().Selects don't really have a purpose. Since the PivotTables are refreshed by name, you don't need to select any cells.

Finally, if you really want, the whole macro can be reduced to a single line :

ThisWorkbook.RefreshAll

Which refreshes all the PivotTables in the current workbook (ThisWorkbook is designated name for the workbook that the code is in.

 

[PipBoy808]

Thanks for the response. I had a feeling it was a little more complicated than it needs to be. I can't help but feel that there's a reason the code is that complicated though. It's actually the fourth iteration of a pivot refresh macro in this workbook. The other three are even longer!

 

[Luke M]

I would hazard a guess then that someone just recorded themselves refreshing the PivotTables, and took several tries to get it right through recording. I often see macros bloat up in size due to row/column scrolling and needless cell selecting.

 

[PipBoy808]

I guess that's possible. Although, the person who created the entire workbook would have been very skilled with Excel. It's possible that the person who wrote the macro was not the person who wrote the general tool.