Results 1 to 5 of 5

Thread: Help to check sheet name then do something

  1. #1
    Board Regular
    Join Date
    Nov 2017
    Posts
    72
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Help to check sheet name then do something

    Hi all I have code below work well but I want to change this line:
    If Sh.Name <> "LocKH" And Sh.Name <> "Ma" Then
    to

    If Sh.Name = array(A,B,C,D,E,F) Then

    Please help me, thanks./.

    Code:
    Option Explicit
    Sub filter()
        Application.ScreenUpdating = False
        Dim a, b(1 To 65000, 1 To 15), lR, Sh As Worksheet, endR As Long, i As Long, j As Long, k As Long
        Dim ii As Long, Data As Variant
    
        Sheets("LocKH").Range("b5:q5000").ClearContents
        endR = Sheets("Ma").Range("B" & Rows.Count).End(3).Row + 1
        
        If endR < 4 Then Exit Sub
        Data = Sheets("Ma").Range("B3:B" & endR).Value    '
        endR = UBound(Data) - 1
        For Each Sh In ThisWorkbook.Worksheets
            If Sh.Name <> "LocKH" And Sh.Name <> "Ma" Then    '
                'do something
            End If
        Next
        Application.ScreenUpdating = True End Sub

  2. #2
    Board Regular steve the fish's Avatar
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,668
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Help to check sheet name then do something

    What do you mean? Your sheets are called A or B or C etc?
    Looking for opportunities

  3. #3
    Board Regular
    Join Date
    Nov 2017
    Posts
    72
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help to check sheet name then do something

    Quote Originally Posted by steve the fish View Post
    What do you mean? Your sheets are called A or B or C etc?
    Sheets in array(A,B,C,D,E,F) => this mean all sheets name call A,B,C,D,E and F

  4. #4
    Board Regular
    Join Date
    Jan 2018
    Posts
    266
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Help to check sheet name then do something

    Quote Originally Posted by sbv1986 View Post
    Hi all I have code below work well but I want to change this line:
    If Sh.Name <> "LocKH" And Sh.Name <> "Ma" Then
    to

    If Sh.Name = array(A,B,C,D,E,F) Then

    Please help me, thanks./.
    Code:
    if not iserror(application.match(Sh.name,split("A,B,C,D,E,F",","),0))
    Last edited by MoshiM; Oct 1st, 2019 at 06:38 AM.

  5. #5
    Board Regular steve the fish's Avatar
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,668
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Help to check sheet name then do something

    What about this:

    Code:
    shArr = Array("Sheet1", "Sheet5")
    
    For i = LBound(shArr) To UBound(shArr)
        Set sh = Sheets(shArr(i))
        MsgBox sh.Name
    Next
    Looking for opportunities

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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