VBA Filter Table based on Cell Value on Another Sheet

[Melimob]

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)

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

 

[mrshl9898]

So the country name is in C4?

Try:

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

 

[Melimob]

So the country name is in C4?

Try:

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

 

[mrshl9898]

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

Depends how they move it.

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

 

[Sleeplol]

Thank you Melimob for asking the question.

Many thanks to mrshl9898 for your script, exactly what i needed