Loopi through worksheets

[Wheelie686]

I am trying to loop through the first 4 sheets of a workbook and for some odd reason, it only seems to be doing it on Sheet 1.
Ideally I'd like it to look by the sheet index but I also tried an array with the names... neither one works.

Maybe a typo I just haven't picked up on?

Sub Test()
Dim Sh As Worksheet
For Each Sh In Sheets(Array("Consumer", "Business", "Commercial", "Escalations"))
    Range("A2:C182").Select
Next
End Sub

I modified the code to simply select the range just to keep it simple for now.

 

[mohadin]

Hi
At least
you should
Sh.Range(....

 

[Wheelie686]

I've tried that as well and it gives me
Runtime Error1004
Select Method of Range class failed

 

[mohadin]

That's right
because you select the range
try to copy the range it would be ok
so avoid select

 

[Michael M]

Try doing it this way.... add the code to do whatever you need to the range

Sub Test()
Dim Sh As Worksheet
For Each Sh In Sheets(Array("Consumer", "Business", "Commercial", "Escalations"))
    With Range("A2:C182")
    '.Do Stuff
    End With
Next
End Sub

 

[Wheelie686]

Thanks Michael M. That did the trick.

 

[Peter_SSs]

Thanks Michael M. That did the trick.

Surely you must have made some significant change as the suggested code will still only act on the active sheet?

Your initial request was to process the first 4 sheets in the workbook. You could do it like this without having to name the sheets.

Sub First4Sheets()
  Dim i As Long
 
  For i = 1 To 4
    With Sheets(i).Range("A2:C182")
      'Do something, for example, put a border around
      .BorderAround xlContinuous
    End With
  Next i
End Sub

In most cases in vba there is no need to Select a range to work with it but if for some reason you do need to select, then you would need to Activate each sheet in turn before selecting a range since selection can only apply to the Active Sheet.