For Each Sheet in Workbook

[Lloyd]

I need to check the same range.value in each sheet in my workbook.
Dim Sh As Object
Sh=Worksheet
For Each Sh in Workbook
But VBA asks for an Object, apparently not explicit enough. What code do
I need to check the same range.value on each worksheet??
TY
Lloyd


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Alex@JPCS]

Lloyd,

Try:

For each sh in Thisworkbook.Sheets
msgbox sh.Range("A1").value
Next sh


No need to Dim the Worksheet.
By the way, its not Sh=Worksheet.


To set a variable equal to a worksheet value I would use:

Dim sh as Worksheet
Set sh=Worksheets("DataValues")
msgbox sh.range("A1").value

HTH,
Alex@JPCS

 

[Tom Ogilvy]

Dim sh as Worksheet
Dim rng as Range
for each sh in thisWorkbook.worksheets
set rng = sh.range("A1:B10")
for each cell in rng

Next
Next

 

[Tom Ogilvy]

Better to declare sh as worksheet, but not required

Sub Tester9()
Dim sh As Object
For Each sh In Worksheets
MsgBox sh.Name
Next
End Sub


works fine as well as

Sub Tester9()
Dim sh As Variant
For Each sh In Worksheets
MsgBox sh.Name
Next
End Sub

or

Sub Tester9()
Dim sh
For Each sh In Worksheets
MsgBox sh.Name
Next
End Sub


or

Sub Tester9()
' no declaration (if option explicit is not declared)
For Each sh In Worksheets
MsgBox sh.Name
Next
End Sub

 

[Lloyd Peck]

Thanks for your reply, I hadnt thought of nesting a For Each structure,
accessing each sheet and then respective range in turn.
Question,, can I declare a range using this same structure which will
include each range on each sheet??
Such as

Range(MyRange)=Sh(a).Range(M1),Sh(b).Range(M1),Sh(c).Range(M1).....
Kind of like a 3D range??
Will VBA do that?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Tom Ogilvy]

No. Range objects have a parent - so a range can't refer to but one sheet.

Depending on what you are going to do you might be able to do this

Worksheets.Select
Range("M1").Select
Selection.Value = 21
worksheets(1).Select



or for a subset of sheets

Worksheets(Array("sheet1", "sheet3", "sheet5")).Select
Range("M1").Select
Selection.Value = 21
Worksheets(1).Select

Regards,
Tom Ogilvy