Excel 2010 - This piece of code seems slow, can it be improved?

[PWD]

Dim PDLBV As String
Dim JRTBV As String
PDLBV = PersonalDetailsListBox.Value
JRTBV = JobRoleTextBox.Value

Application.ScreenUpdating = False

 EvidenceRowsCount = Range("Evidence").Rows.Count
 
        For Each wrksht In ThisWorkbook.Worksheets
            If wrksht.Index > 1 And wrksht.Index <> ThisWorkbook.Worksheets.Count _
            And wrksht.Index <> ThisWorkbook.Worksheets.Count - 1 Then

    Application.StatusBar = "Updating Worksheet " & wrksht.Name

                wrksht.Unprotect ("cpd")

                If wrksht.Index = 2 Then
                wrksht.Range("D3").Value = PDLBV ''Name
                wrksht.Range("D5").Value = JRTBV ''Job Role
                End If

If I just step through the code (F8) you can see it thinking about adding the values in each of D3 & D5. How should I be doing this differently?

Many thanks,
D€$

 

[combo]

If it's due to recalculation of the workbook, try to switch off it:
[tt]Application.Calculation = xlCalculationManual
' your code
Application.Calculation = xlCalculationAutomatic[/tt]
However. I have a macro that does not like some code in other workbook with no event procedures, just a simple Sub... It executes very slowly when the other is opened.


combo

 

[zelgar]

If wrksht.Index > 1 And wrksht.Index <> ThisWorkbook.Worksheets.Count _
And wrksht.Index <> ThisWorkbook.Worksheets.Count - 1 Then

can be reduced to

If wrksht.Index > 1 And wrksht.Index < ThisWorkbook.Worksheets.Count - 1 Then

 

[SkipVought]

Use the With...End With construct

    For Each wrksht In ThisWorkbook.Worksheets  '[b]
        With wrksht                             '[/b]
            If .Index > 1 And .Index < ThisWorkbook.Worksheets.Count - 1 Then
    
                Application.StatusBar = "Updating Worksheet " & .Name
    
                .Unprotect ("cpd")
    
                If .Index = 2 Then
                    .Range("D3").Value = PDLBV ''Name
                    .Range("D5").Value = JRTBV ''Job Role
                End If
            End If
        End With
    Next

 

[SkipVought]

Also to add to the calculation issue that combo suggested:

If you do have NECESSARY calculations on a sheet, meaning that the calculated values/ranges on the sheet are referenced by other cells in your code, then if you have used named ranges, you can calculate those ranges explicitly when appropriate. It means that you must have documented and understand how your workbook works.

But then, of course, you must recalculate the entire workbook before it is saved.