Excel 2010 Alternative to Selecting Sheets Sought 1

[PWD]

Good morning, I know the following has too much "Select" in it but I needed something quick. I just don't really like it.

Some of the sheets were hidden so I unhid them but this would have applied to them all anyway.

There are variable numbers of columns in the sheets and we wanted to insert a formula that reflected the percentage of those that have been completed.

Sub D6_Formula()

Dim ws As Worksheet
Dim MyAddress As String
Dim MyCol As Integer

        For Each ws In ActiveWorkbook.Worksheets
            If ws.Index > 2 And ws.Index <> ThisWorkbook.Worksheets.Count Then

                Sheets(ws.Index).Select
                Range("G8").Select
                MyAddress = Split(Selection.End(xlToRight).Address(1, 0), "$")(0) ''End Column Letter
                MyColumn = Selection.End(xlToRight).Column  ''End Column Number
                Range("D6").Formula = "=Sum(G8:" & MyAddress & "8)/" & MyColumn - 6
           ''   =SUM(G8:AD8)/24
            End If
        Next ws

End Sub

Could I have started with a "With"?



Many thanks,
D€$

 

[combo]

Is ActiveWorkbook and ThisWorkbook the same object? if so, it would be more secure to work with ThisWorkbook only.
Sheets(ws.Index) IS Ws, so you can use directly Ws.Select.
To simplify code inside If...End If:

With Ws.
    .Range("D6").Formula = "=Average(" & .Range(.Range("G8"), .Range("G8").End(xlToRight)).Address(1, 0) & ")" 
End With

To give feedback about the progress, count sheets/columns first and use StatusBar to pass messages from the loop (if the code is slow enough to wait for completing the task).

combo

 

[PWD]

I'll give it a go. Thanks.

Many thanks,
D€$

 

[zelgar]

Here's the least amount of code to do what you want:

Sub D6_Formula()

Dim ws As Worksheet
        For Each ws In ActiveWorkbook.Worksheets
            If ws.Index > 2 And ws.Index <> ThisWorkbook.Worksheets.Count Then
                With ws
                    ws.Range("D1").Formula = "=Sum(G8:" & Split(ws.Range("G8").End(xlToRight).Address(1, 0), "$")(0) & "8)/" & ws.Range("G8").End(xlToRight).Column - 6
                End With
            End If
        Next ws
End Sub

 

[PWD]

Looks very good. Cheers!!

Many thanks,
D€$