Consulting

Results 1 to 8 of 8

Thread: Select Sheets Array If

  1. #1

    Question Select Sheets Array If

    Hi,

    I want to write a code to select multiple sheets in my workbook but only if condition is met for most of them.
    Example, I want that VBA selects first 3 sheets and for the rest of the 29 sheets only if cell value in I1 = YES; if value = NO, then not to select that sheet.

    ThisWorkbook.Sheets(Array(MyArray)).Select
    HOW TO DEFINE MyArray?
    Sheets("CoverPage").Select
    Sheets("MRAge").Select
    Sheets("MROverall").Select

    Sheets("MRD1").Activate
    Dim strName As String
    strName = "I1"
    If strName = "YES" Then
    Sheets("MRD1").Select
    End If

    Sheets MR I have from 1 to 29 and YES/NO should be applied for them.

  2. #2
    VBAX Mentor 大灰狼1976's Avatar
    Joined
    Dec 2018
    Location
    SuZhou China
    Posts
    479
    Location
    Hi FeatherM!
    Welcome to vbax forum.
    No tested
    sub test()
    dim arr, n&, sh&
    arr=array("CoverPage", "MRAge", "MROverall")
    n=2
    for sh=4 to sheets.count
      with sheets(sh)
        if .[i1] = "YES" then
          n=n+1
          redim preserve arr(n)
          arr(n)=.name
        end if
      end with
    next sh
    ThisWorkbook.Sheets(arr).Select
    end sub

  3. #3
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    or:
    Sub blah()
    Sheets(1).Select
    For i = 2 To Sheets.Count
          If UCase(Sheets(i).Range("I1").Value) = "YES" Or i < 4 Then Sheets(i).Select False
    Next i
    End Sub
    Last edited by p45cal; 04-03-2019 at 11:12 AM.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  4. #4
    Hi,
    Tried your code and run perfect! Thanks!

  5. #5
    Hi 大灰狼1976,
    Your code run perfect! Thanks!

  6. #6
    Hi,

    Need help on Redim Preserve arr(n).
    I am running practically the same in new workbook, but in this case the Sheet name is numbers, as 103, etc.
    And for some reason the code doesnt work any longer....

    Any advice?

    For example selection separately works:

    For Each List3 In wsCE1.Range("F10:F" & LastrowAct) 'works selection
    If List3.Value <> 0 Then
    Sheets(CStr(List3.Value)).Select
    End If
    Next List3

    But not in the code:

    For sh = 8 To Sheets.Count
    With Sheets(sh)
    For Each List3 In wsCE1.Range("F10:F" & LastrowAct)
    If List3.Value <> 0 Then
    'Sheets(CStr(List3.Value)).Select
    n = n + 1
    ReDim Preserve arr(n)
    End If
    Next List3
    End With
    Next sh

  7. #7
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    For sh = 8 To Sheets.Count
        With Sheets(sh)
            For Each List3 In wsCE1.Range("F10:F" & LastrowAct)
               If List3.Value <> 0 Then
                  'Sheets(CStr(List3.Value)).Select
                  n = n + 1
                  ReDim Preserve arr(n)
              End If
          Next List3
        End With
    Next sh
    That makes no sense. That code says:
    For Certain Sheets
       For each Item on a particular sheet
          '
          ' do stuff to an Array
          '
       Next Item
    Next Sheet
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  8. #8
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Sub M_snb()
      For j = 1 To Sheets.Count
        If j < 4 Or Sheets(j).Cells(1) = "a" Then c00 = c00 & "|" & Sheets(j).Name
      Next
    
      Sheets(Split(Mid(c00, 2), "|")).Select
    End Sub

Posting Permissions

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