+ Reply to Thread
Results 1 to 4 of 4

Amend macro to run through all sheets except two

Hybrid View

  1. #1
    Registered User
    Join Date
    09-11-2008
    Location
    Scotland
    MS-Off Ver
    Excel 2002
    Posts
    8

    Amend macro to run through all sheets except two

    Hi everyone,

    I have found and adapted a macro (below) that searches all worksheets for a specific value (entered by the user in a specfic cell) then displays a list of hyperlinks to the matching cell locations.

    I now have a couple of sheets I don't want to be included in this macro ("Master List" and "Pivot Table") but I'm really struggling to find a way of amending the macro to exclude these. I've searched and tried a couple of ways but I get errors and I think I'm not knowledgable enough to know where to put each bit of code.

    Any help gratefully accepted

    Private Sub Search_Click()
    ' finds and displays list of locations for strain entered in Search!D10, hyperlinks to drawer map
    Range("C24:C500").Select
        Selection.Clear
    Dim findvar As String
    Dim sht
    Dim cl
    Dim outputvar As Long
    findvar = Sheets("SEARCH").Range("D10").Value
    outputvar = 0
    For Each sht In ActiveWorkbook.Sheets
       If sht.Name <> ActiveSheet.Name Then
           For Each cl In sht.UsedRange
               If InStr(cl.Value, findvar) <> 0 Then
                   Sheets("SEARCH").Range("C24").Offset(outputvar, 0).Formula = _
                           "=HYPERLINK(""#'" & cl.Parent.Name & "'!" & cl.Address & """,""" & cl.Parent.Name & " - " & cl.Value & """)"
                   outputvar = outputvar + 1
               End If
           Next
       End If
    Next
    Range("D10").Select
    End Sub
    Last edited by rajsa; 02-05-2009 at 11:24 AM.

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Amend macro to run through all sheets except two

    I can't test this without the workbook so try this on a backup file

    Option Explicit
    
    Private Sub Search_Click()
        ' finds and displays list of locations for strain entered in Search!D10, hyperlinks to drawer map
        Range("C24:C500").Select
        Selection.Clear
        Dim findvar As String
        Dim sht
        Dim cl
        Dim outputvar As Long
        findvar = Sheets("SEARCH").Range("D10").Value
        outputvar = 0
        For Each sht In ActiveWorkbook.Sheets
            Select Case sht.Name
                Case ActiveSheet.Name, "Master List", "Pivot Table"
                Case Else
                    For Each cl In sht.UsedRange
                        If InStr(cl.Value, findvar) <> 0 Then
                            Sheets("SEARCH").Range("C24").Offset(outputvar, 0).Formula = _
                            "=HYPERLINK(""#'" & cl.Parent.Name & "'!" & cl.Address & """,""" & cl.Parent.Name & " - " & cl.Value & """)"
                            outputvar = outputvar + 1
                        End If
                    Next
            End Select
        Next
    
        Range("D10").Select
    End Sub
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Registered User
    Join Date
    09-11-2008
    Location
    Scotland
    MS-Off Ver
    Excel 2002
    Posts
    8

    Re: Amend macro to run through all sheets except two

    That works perfectly, thank you! It's a solution I haven't come across elsewhere, I will have a proper look at what it does and hopefully learn something

  4. #4
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Amend macro to run through all sheets except two

    post back if you are not sure of anything

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1