Excel: Looping thru all sheets

[Stretchwickster]

I'm new to VBA so can anyone tell me how I can loop through all non-hidden worksheets that are open and select a certain cell within each worksheet. I've had a go but keep getting errors. Below is my attempt (but it produces a range error):

If Sheets.count > 0 Then
  Sheets(1).Select
  For Counter = 1 To Sheets.count
    Range("A1").Select
    If Counter = Sheets.count Then
      End
    End If
    ActiveSheet.Next.Select
  Next Counter
End If

Clive

 

[VulcanJedi]

If Sheets.count > 0 Then
Sheets(1).Select
For Counter = 1 To Sheets.count
Range("A1&quot.Select
If Counter = Sheets.count Then
End END WHAT? this is why you have errors, I would assume you want exit for, but I don't understand why you have this if statement at all??!

End If
ActiveSheet.Next.Select
Next Counter
End If


TRY THIS
If Sheets.count > 0 Then
For Counter = 1 To Sheets.count
sheets(counter).Range("A1&quot.Select

Next Counter
End If


hope this helps Tranpkp

 

[RobBroekhuis]

You can do it much simpler:

for each sh in sheets
if sh.visible then sh.range("A1&quot.select
next sh

Rob

 

[VulcanJedi]

2 lines Rob, two lines!
Rob, we are both so very very sad! [smiley] Tranpkp

 

[Stretchwickster]

Sorry guys...neither method works!!

I got a run-time error '1004'
'Select method of Range class failed'

I have highlighted the offending lines below:

Rob:

for each sh in sheets
if sh.visible then sh.range("A1&quot.select
next sh

Tranpkp:

If Sheets.count > 0 Then
For Counter = 1 To Sheets.count
sheets(counter).Range("A1&quot.Select
Next Counter
End If

Could the problem be that some sheets are open but hidden? Clive

 

[VulcanJedi]

Clive I've reproduced your error, I admit I am confused I know you can't SELECT stuff from a hidden sheet, but on a visible sheet you should, to offer a solution however, I find it very rare that I need to SELECT a cell, if you simply want to obtain data from the cell, use rob's revised code and do :
sh.cells(1,1).value (this worked for me)


Tranpkp

 

[Stretchwickster]

Ok, what I'm trying to do is this:

I'm amending someone else's code and the ouput of this code leaves some ranges selected on each sheet, probably because each range was the last one to be operated upon. I wanted to simply tidy up what the user sees by deselecting these ranges i.e. by making "A1" the active cell on each sheet. Some of the sheets are created dynamically, so there's never a fixed number of sheets. Clive

 

[xlbo]

Try this
For each sht in activeworkbook.worksheets
if sht.visible = true then
sht.range("A1&quot.select
else
end if
next

OR

the alternative (which would probably speed up the code) is to replace all the select statements within the original code and use WITH statements which shouldn't leave any cells selected Rgds
~Geoff~

 

[Stretchwickster]

Thanks for the quick responses but you all missed one line of code which makes it all run perfectly:

Dim wksh As Worksheet

For Each wksh In ActiveWorkbook.Worksheets
If wksh.Visible = True Then
wksh.Activate
wksh.Range("A1&quot.Select
Else
End If
Next

Clive

 

[RobBroekhuis]

Man! Goes to show we should try what we recommend before posting
By the way, my pet peeve of the day:
(boolean expression) = true
really grates on me! The "= true" is superfluous, and sets my hairs on end [end pet peeve]
Rob

 

[Stretchwickster]

Sorry Rob - I quite understand. I was just using the code that finally worked but here is the code with pet peeves removed and as tidy as I can make it ;-):

Dim wksh As Worksheet

For Each wksh In ActiveWorkbook.Worksheets
If wksh.Visible Then
wksh.Activate
wksh.Range("A1&quot.Select
End If
Next

Clive