VBA Filter Table based on Cell Value on Another Sheet

Melimob

Active Member
Joined
Oct 16, 2011
Messages
365
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 :)
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,247
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:

Melimob

Active Member
Joined
Oct 16, 2011
Messages
365
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
 

Sleeplol

Board Regular
Joined
Apr 10, 2019
Messages
89
Thank you Melimob for asking the question.

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

Watch MrExcel Video

Forum statistics

Threads
1,101,817
Messages
5,483,080
Members
407,378
Latest member
swanbrown

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top