Qualifying Workbooks And Worksheets

mtv20 · Feb 7th 2007

Hi
I am quite new to VBA and I am having a bit difficulty when it comes to qualifying the objects in looping.Can anyone tell me why the following code gives an error? How do I make this loop to work with all open workbooks and all sheets in every workbook? Thanks

Code

Sub referencing()
Dim wks As Worksheet
Dim wkb As Workbook
For Each wkb In Workbooks
    For Each wks In Worksheets
        wkb.wks.Cells(1, 2) = 5
    Next wks
Next wkb


End Sub

Display More

mtv20 · Feb 7th 2007

Re: Qualifying Workbooks And Worksheets

To make things more clear:
What i find confusing is that the following code does the job and the only difference is that I activate the workbook. However in many references, I've seen that there is no need to select or activate a workbook in order to work with it.
Can anyone give me an explanation? Thanks

Code

Sub referencing() 
    Dim wks As Worksheet 
    Dim wkb As Workbook 
    For Each wkb In Workbooks 
wkb.activate
        For Each wks In Worksheets 
            wkb.wks.Cells(1, 2) = 5 
        Next wks 
    Next wkb 
     
End Sub

Display More

**StephenR** · Feb 7th 2007

Re: Qualifying Workbooks And Worksheets

Does this work? I think you have to put it like this just because that's the way you reference the collection of open workbooks. But if you activate it you don't need to reference it at all. Someone else can probably give you a better explanation!

Code

Sub referencing() 
    Dim wks As Worksheet 
    Dim wkb As Workbook
    Dim i As Integer
    For i = 1 to Workbooks(Workbooks.Count)
        For Each wks In Worksheets 
            wkb(i).wks.Cells(1, 2) = 5 
        Next wks 
    Next i
End Sub

mtv20 · Feb 7th 2007

Re: Qualifying Workbooks And Worksheets

Thanks StephenR but that gives an error as well.I think it has to be workbooks.count only not workbooks(workbooks.count) but still it gives error.

**StephenR** · Feb 7th 2007

Re: Qualifying Workbooks And Worksheets

Sorry, that's wrong. Having a young middle-aged moment.

Anonymous · Feb 8th 2007

Re: Qualifying Workbooks And Worksheets

Quote from StephenR

Sorry, that's wrong. Having a young middle-aged moment.

Ah! I must remember that excuse, except mine are old-aged moments...

**royUK** · Feb 8th 2007

Re: Qualifying Workbooks And Worksheets

It is generally not necessary to select or activate an object, however there are exceptions to any rule.

Try this, not I have removed wkb from wkb.ws, which was causing the "Object does not support this property or method error." I have switched off screen updating.

Code

Option Explicit


Sub referencing()
    Dim wks As Worksheet
    Dim wkb As Workbook
    Application.ScreenUpdating = False
    For Each wkb In Workbooks
    wkb.Activate
        For Each wks In Worksheets
            wks.Cells(1, 2) = 7
        Next wks
    Next wkb
    Application.ScreenUpdating = True
End Sub

Display More

Insomniac · Feb 8th 2007

Re: Qualifying Workbooks And Worksheets

The original code works fine for me with a minor adjustment, no Workbook Activation is needed.
The line For Each wks In Worksheets will always refer to the Application.ActiveWorkbook without explicit qualification.
Hence: For Each wks In wkb.Worksheets
The wks object variable when fully referenced knows what its parent Workbook object is without activating it.

Code

Sub ReferencingFixed()
    Dim wks As Worksheet
    Dim wkb As Workbook
    For Each wkb In Workbooks
        For Each wks In wkb.Worksheets
            wks.Cells(1, 2) = 5
        Next wks
    Next wkb
End Sub

mtv20 · Feb 8th 2007

Re: Qualifying Workbooks And Worksheets

Thanks a lot , finally it has been solved without activation. I have 33 workbooks with 10 sheets in each so activation was really an issue since it makes the code slower I guess.

Qualifying Workbooks And Worksheets

Participate now!

Share