Each Sheet in Workbook

[Chris Watts]

I have three workbooks open and wish to limit the scope of operation to all
the sheets in one workbook.
Is there a construct something like:

Dim WS As Worksheet
For Each WS in Workbook("Workbook name") ' or in ActiveWorkbook
' Do something
Next WS

I cannot find anything just WS in Worksheets which is too broad.
I guess though I could do something like:

For Each WS in Worksheets
If (WS.Parent.Name<>ActiveWorkbook.Name) Then Go To NextWS
'Do something
NextWS:
Next WS

I am using VBA in Excel 2007

TIA
Chris

 

[Jim Cone]

For Each WS in ThisWorkbook.Worksheets
'more code
Next
--
Jim Cone
Portland, Oregon USA .
http://www.mediafire.com/PrimitiveSoftware .
(Extras for Excel add-in: convenience built-in)




"Chris Watts" <[email protected]>
wrote in message
news:[email protected]...

 

[GS]

Chris Watts formulated on Monday :

I have three workbooks open and wish to limit the scope of operation to all
the sheets in one workbook.
Is there a construct something like:

Dim WS As Worksheet
For Each WS in Workbook("Workbook name") ' or in ActiveWorkbook
' Do something
Next WS

I cannot find anything just WS in Worksheets which is too broad.
I guess though I could do something like:

For Each WS in Worksheets
If (WS.Parent.Name<>ActiveWorkbook.Name) Then Go To NextWS
'Do something
NextWS:
Next WS

I am using VBA in Excel 2007

TIA
Chris

Create an object reference to the target workbook and use that for any
operations on that workbook.

Sub DoStuff()
Dim wkbTarget As Workbook
Set wkbTarget = Workbooks("Workbook Name.xls")
With wkbTarget...
'do stuff
End With
Set wkbTarget = Nothing 'cleanup
End Sub

 

[Chris Watts]

Thanks Jim and Gary for your suggestions.

Chris

 

[GS]

Chris,

Jim showed you how to reference the workbook containing the code you're
running.

I showed you one way to reference a specific (open) workbook.

To iterate the sheets in either case...

Dim wks As Worksheet

For Each wks In ThisWorkbook.Worksheets
'...
Next 'wks

OR

For Each wks In wkbTarget.Worksheets
'...
Next 'wks