Results 1 to 7 of 7
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Select all sheets in a workbook via VBA (Excel 2003)

    When working in a workbook one can group sheets and then get your actions repelled through ALL the sheets simultaniously. When I record this the worksheets are selected by using the array function. But I do not know how to set this up to select ALL the sheets if the number of sheets is unknown or volatile. I could use a loop such as For Each Sheet, but I would like to know if and how to do this grouping thing via VBA. It seems faster than having to loop through say 50 sheets doing the same actions on each sheet.

    TX
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Select all sheets in a workbook via VBA (Excel 2003)

    Why would you want to repel actions? <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

    The simple instruction Sheets.Select selects all sheets.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Select all sheets in a workbook via VBA (Excel 2003)

    OK...as easy as that.
    Once all the sheets are selected, what you do on one applies to the others.

    TX
    Regards,
    Rudi

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Select all sheets in a workbook via VBA (Excel 2003)

    I would call that repeat, not repel actions.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Select all sheets in a workbook via VBA (Excel 2003)

    The duplicating on all sheets is when you manually do actions when they are all selected.

    If in code you change a cell, it is either implicitly done to the active sheet or explicitly done to a particular sheet. If you want the actions in code to work on all the selected sheets, you must loop thru the selected sheets and do the actions on each one. [This is what XL does if you have multiple sheets selected, but VB does not do this...]

    Steve

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Select all sheets in a workbook via VBA (Excel 2003)

    Hans:
    I suppose repell was not in the right context. I could also have used drill... To have the details drill through the rest of the selected sheets. Cheers

    Steve:
    Thanks for that pointer. I was not aware of this and was planning to do something like this. I guess your reply prevents me from going through the effprt of making that mistake.
    Regards,
    Rudi

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Select all sheets in a workbook via VBA (Excel 2003)

    To repel = to drive back, to rebuff, to disgust.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •