Array of Worksheet Names

  • I'm having a problem with the following array. It will bug out on every line marked with an <<<

    May I ask what I am doing wrong? I've tried every trick I know, but I'm obviously no magician...

    arrSheets = Array("BR", "mp", "R1", "R3", "R4", "R5", "R6", "R2", "I", "I2", "I3", "I4", "I5", _
            "I6", "I7", "I8", "I9", "I10", "I11", "I12")
    For i = LBound(arrSheets) To UBound(arrSheets)
        Sheets(arrSheets(i)).Interior.ColorIndex = xlNone<<<
        Sheets(arrSheets(i)).DisplayHeadings = True<<<
    Next i

    Thanks in advance!

  • Re: My daddy always said I'd go array...

    It would appear that you want the array to contain the names of sheets but it contains a mixture of possible names and ranges....

    The methods you are executing are for ranges not for the sheet object. Can you describe what you are trying to acheive and give some parameters?


  • Re: My daddy always said I'd go array...

    with those sheets i am trying to:

    remove color
    display headers
    remove conditional formatting
    select range a1

  • Re: My daddy always said I'd go array...

    I also tried things like

  • Re: My daddy always said I'd go array...

    ah the master arrives. Sorry Norie, It's a hard lesson to learn.


    Someday I will get it through my head.

    I used it because everything else I tried didn't work... did you see, at first, I didn't use select? I am trying :grin:

  • Re: Array of Worksheet Names

    Sorry, go caught up at work...

    No you dont need to select to do most things however some things do need to be active to set properties:

    1) I mentioned before the methods you were attempting were for cell ranges hence the .Cells qualifier
    2) In ordr to set the DisplayHeadings setting I activated each window as I looped through the array - cant promise its the best way but it works with little to no performance drop...
    3) There is no humour in arrays! :)


  • Re: Array of Worksheet Names

    cool - thanks very much pesky - i had a ferret once - to say pesky is an understatement - wa sa lot fo fun tho he was :)

Participate now!

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