Qualifying Workbooks And Worksheets

  • 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

  • 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


  • 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
  • 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.

  • 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.


  • 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
  • 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.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!