VBA Loop and Refresh (shift+F9)

[pstreett]

Hi All,

I have an excel file with multiple tabs, 9 to be exact, and want to be able to refresh all, starting with the last tab and moving backward.

The refresh is in conjunction with IBM TM1, so I believe I have to use "TM1Recalc1" as the refresh.

I currently have the below, but I don't think it's efficient or as simple as it should be. Any ideas?


Sub TrendUpdate()


Dim WS_Count As Integer
Dim I As Integer


' Set WS_Count equal to the number of worksheets in the active
' workbook.
WS_Count = ActiveWorkbook.Worksheets.Count


' Begin the loop.
For I = 1 To WS_Count

Sheets(I).Select
Application.Run "TM1Recalc1"

Range("A2").Select



Next I


Sheet1.Select
Application.Run "TM1Recalc1"
Range("A1").Select

'SS Data refresh
Sheet9.Select
Sheet9.Calculate
Range("A1").Select

'MTD Total & Regions refresh
Sheet1.Select
Sheet1.Calculate
Range("A1").Select

Sheet2.Select
Sheet2.Calculate
Range("A1").Select

Sheet3.Select
Sheet3.Calculate
Range("A1").Select

Sheet4.Select
Sheet4.Calculate
Range("A1").Select

Sheet5.Select
Sheet5.Calculate
Range("A1").Select

Sheet6.Select
Sheet6.Calculate
Range("A1").Select

Sheet7.Select
Sheet7.Calculate
Range("A1").Select

Sheet8.Select
Sheet8.Calculate
Range("A1").Select




End Sub

 

[JackDanIce]

Welcome to the board. You should very rarely need to use .Select in your code, it isn't required and slows macro execution. It may be worth posting the code for TMIRecalc1. For now, try:

Sub TrendUpdate()

    Dim x       As Long
    Dim wsCount As Long
    
    wsCount = ActiveWorkbook.Worksheets.count
    
    Application.ScreenUpdating = False
    
    For x = wsCount To 1 Step -1
        With Sheets(x)
            .Select
            Application.Run "TMIRecalc1"
            .Calculate
            .Range("A1").Select
        End With
    Next x
    
    Application.ScreenUpdating = True
        
End Sub