Speed Up Slow Excel VBA Macro

[madhattt]

I have a workbook that has around 125 sheets and it is always changing. I use the second sheet as an Index. This macro below runs when I activate the Index Sheet. It takes around 4 seconds to run through. I use it very often and the time really adds up. Does anyone have any suggestions on how to speed it up? Any little bit will save me a lot of time!

---

Private Sub Worksheet_Activate()

Dim sheetnum As Integer, i As Integer, j As Integer, sheetname As String


Application.ScreenUpdating = False
Application.EnableEvents = False

Range("A:A").ClearContents
Range("B:B").ClearContents
Range("A1").Value = "WPS INDEX"

sheetnum = ActiveWorkbook.Sheets.Count
j = 2

For i = 1 To sheetnum
    sheetname = ActiveWorkbook.Sheets(i).Name
    If sheetname = "WPS INDEX" Then
      Else
        Cells(j, 1).Value = sheetname    'this part inserts the sheet name in column A
        ActiveSheet.Hyperlinks.Add Cells(j, 1), "", "'" & sheetname & "'" & "!A1"    'this part adds the hyperlink
        Cells(j, 2).Value = i    'this part adds the sheet number to column B
        j = j + 1
    End If
Next i

Cells(1, 1).Select
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 1
ActiveWindow.Zoom = 120

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

Thank you in advance for any suggestions!!!!

 

[Redwolfx]

I'm not the most knowledgable, but it looks like ou have a pretty streamlined code. The only thing I can think of is if you have calculations running, it MIGHT be slowing things down.

If you add

Application.Calculation = xlCalculationManual ' With the Screenupdating
Application.Calculation = xlCalculationAutomatic ' at the end

It MAY speed things up a little.

 

[madhattt]

THAT WAS IT!!! Thank you. It is taking no time at all now. As fast as the click of the mouse. Great advice. I would have never thought calculation had anything to do with it. Thank you!! You saved me an incredible amount of time with this!

 

[Redwolfx]

Glad that solved the issue. Thanks for the feedback