none
Loop through array of Excel sheets RRS feed

  • Question

  • I'm looping through an array of Excel sheets like you see below.

                AllTheSheets = Array("Service Taxonomy", "Personnel&Facilities Validat", "Personnel&Facilities Detail", "Systems Mapping Part 1", _                "Systems Mapping Part 2", "Systems Detail", "Vendor & Memb Mapping Part 1", "Vendor & Memb Mapping Part 2", "Vendor & Memb Mapping Detail", "Substitutability")                        For Each SingleSheet In AllTheSheets                    Sheets(AllTheSheets).Select

    Pretty simple. Now, however, I'm encountering an issue. I just want to perform a task on one sheet at a time, as I loop through all.  However, the script is doing the work on all the sheets because they are all selected.  For instance, I'm trying to do a sort on the first sheet and I can't because all sheets are selected, so this feature is disabled. 

    I just didn't want a really long script, so I'm truing to cut down on the amount of code that I have to maintain.  Is there an easier way to do this?


    MY BOOK

    Sunday, July 31, 2016 3:03 AM

Answers

  • Pretty simple.

    I feel like a preacher in the desert:

    Please never use SELECT, SELECTION, ACTIVECELL, it is slow and error prone. Always refer to the objects directly.

    Make a new file with at min. 2 sheets and run the macro below.

    Andreas.

    Sub Test()
      Sheets.Select
      
      With Sheets(1)
        .Range("A1") = "Nr"
        .Range("A2") = 9
        .Range("A2:A10").DataSeries xlColumns, xlLinear, , -1
        MsgBox "All sheets are selected, now we sort this data"
        .Range("A2:A10").Sort .Range("A1"), xlAscending
      End With
    End Sub


    Sunday, July 31, 2016 7:11 AM

All replies