Looping through different sheets

Question

I would appreciate your help with the macro I am trying to create.

I have an xls file with a bunch of worksheets, some of which named "1", "2", "3", and so forth. I would like to create a macro that loops only through those 'number-named' worksheets, hence NOT according to the index as in the code below. (Sheet "1" is not the first sheet in the workbook). Before the loop I need to define both the cell range and sheets.

Below is my (wrong) attempt.

Sub Refresh ()

Dim i As Integer
Dim rng As Range
Set rng = Range("A10:TZ180")

 For i = 1 To 30

  Sheets(i).Activate
  rng.Select       
  rng.ClearContents
  Application.Run macro:="xxx"

 Next i

End Sub

Answer 1

dim w as worksheet
for each w in activeworkbook.worksheets
  if isnumeric(w.name) then
    w.range("A10:TZ180").clearcontents
    xxx()
  end if
next

Answer 2

try this

Sub main()
Dim shtNames As Variant, shtName As Variant

shtNames = Array(1, 2, 3, 4) '<== put your actual sheets "number name"

For Each shtName In shtNames
    With Worksheets(CStr(shtName))
        .Range("A10:TZ180").ClearContents
        .Range("A10:TZ180").Select
        Application.Run macro:="MacroToRun"
    End With
Next shtName

End Sub


Sub MacroToRun()

MsgBox "hello from cells '" & Selection.Address & "' in sheet '" & ActiveCell.Parent.Name & "'"
End Sub

Answer 3

If the macro "xxx()" requires a selected range you just need to add a select statement. (Borrowing from GSerg)

Dim w As Worksheet
For Each w In ActiveWorkbook.Worksheets
  If IsNumeric(w.Name) Then
    w.Range("A10:TZ180").ClearContents
    w.Range("A10:TZ180").Select
    Application.Run macro:="xxx"
  End If
Next

To clear up your misunderstanding about assigning a range see the following:

Sub Refresh()
    Dim ws As Worksheet
    Dim rng As Range
    Dim i As Integer

    For Each ws In ActiveWorkbook.Worksheets
        If IsNumeric(ws.Name) Then

            'you must activate the worksheet before selecting a range on it
            ws.Activate

            'note the qualifier: ws.range()
            Set rng = ws.Range("A10:TZ180")

            'since the range is on the active sheet, we can select it
            rng.Select
            rng.ClearContents
            Application.Run macro:="xxx"
        End If
    Next

End Sub

Sub test2()
    Dim ws As Worksheet
    Dim rg As Range

    Dim arrSheets As Variant
    arrSheets = Array("Sheet1", "Sheet2", "Sheet3")

    Dim x As Long
    For x = LBound(arrSheets) To UBound(arrSheets)
        Set ws = Worksheets(arrSheets(x))
        ws.Activate

        '...

    Next
End Sub

Sub test3()
    Dim ws As Worksheet

    Dim x As Long
    For x = 1 To 20
        Set ws = Worksheets(CStr(x))
        ws.Activate

        '...

    Next
End Sub