For Each Sheet in Workbook
 

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!

For Each Sheet in Workbook
 

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



"Lloyd" <jghflkd!!@dghrtyd_tt wrote in message
...
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!

For Each Sheet in Workbook
 

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

--
Regards,
Tom Ogilvy


Lloyd <jghflkd!!@dghrtyd_tt wrote in message
...
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!

For Each Sheet in Workbook
 

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


--
Regards,
Tom Ogilvy


losmac wrote in message
...
Sh (in your example) is not a worksheet, it's object!

Declare like this:
Dim Sh asWorksheet

And everything gonna be OK.

Uzytkownik "Lloyd" <jghflkd!!@dghrtyd_tt napisal w wiadomosci
...
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!

For Each Sheet in Workbook
 

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!

For Each Sheet in Workbook
 

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


Lloyd Peck wrote in message
...

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!