VBA Filter Table based on Cell Value on Another Sheet
Results 1 to 4 of 4

Thread: VBA Filter Table based on Cell Value on Another Sheet

  1. #1
    Board Regular
    Join Date
    Oct 2011
    Posts
    352
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Filter Table based on Cell Value on Another Sheet

    Hi,

    I've hunted around for code for my problem and thought this would work but doesn't.

    I have a table where I want to filter 'Country Name' = to 'Country' value listed on another sheet.

    (Then, copy the data from that table to other cells. - which I think I have worked out)

    Code:
    Option Explicit
    
    Sub Filtering_Country()
        '
        ' Filtering Macro
        '
        Dim Country As Range
    
    
        With Worksheets("Instructions")
            Set Country = .Range("C4")
           
        End With
    
    
        With Worksheets("ImportCCB")
            With .Range("A1:Z" & .Cells(.Rows.Count, "R").End(xlUp).Row)
                .AutoFilter 'Turn off any previous filtering
                .AutoFilter Field:=1, Criteria1:=Country_Name
            End With
            .AutoFilterMode = False
        End With
    End Sub
    Any advice greatly received

  2. #2
    Board Regular
    Join Date
    Feb 2012
    Posts
    869
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA Filter Table based on Cell Value on Another Sheet

    So the country name is in C4?

    Try:

    Code:
    Option Explicit
    
    
    Sub Filtering_Country()
        '
        ' Filtering Macro
        '
        Dim Country As String
    
        Country = Worksheets("Instructions").Range("C4").value
    
        With Worksheets("ImportCCB")
            With .Range("A1:Z" & .Cells(.Rows.Count, "R").End(xlUp).Row)
                .AutoFilter 'Turn off any previous filtering
                .AutoFilter Field:=1, Criteria1:=Country
            End With
            .AutoFilterMode = False
        End With
    End Sub
    Last edited by mrshl9898; Apr 9th, 2019 at 06:46 PM.

  3. #3
    Board Regular
    Join Date
    Oct 2011
    Posts
    352
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Filter Table based on Cell Value on Another Sheet

    Quote Originally Posted by mrshl9898 View Post
    So the country name is in C4?

    Try:

    Code:
    Option Explicit
    
    
    Sub Filtering_Country()
        '
        ' Filtering Macro
        '
        Dim Country As String
    
        Country = Worksheets("Instructions").Range("C4").value
    
        With Worksheets("ImportCCB")
            With .Range("A1:Z" & .Cells(.Rows.Count, "R").End(xlUp).Row)
                .AutoFilter 'Turn off any previous filtering
                .AutoFilter Field:=1, Criteria1:=Country
            End With
            .AutoFilterMode = False
        End With
    End Sub
    thank you so much this! Worked a treat and I was able to use it with another sheet at the same time.
    One question, my Country is in C4 however in case that is moved by someone, is there anyway to name it in the table or account for if the row changes?

    many thanks

  4. #4
    Board Regular
    Join Date
    Feb 2012
    Posts
    869
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA Filter Table based on Cell Value on Another Sheet

    You could potentially set it up as a single cell named range.

    Depends how they move it.

    https://www.excelhelp.com/using-cell...-named-ranges/

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
  •