VBA Filter Table based on Cell Value on Another Sheet

Melimob

Active Member
Joined
Oct 16, 2011
Messages
395
Office Version
  1. 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 :)
 

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
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:
Upvote 0
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
 
Upvote 0
Thank you Melimob for asking the question.

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

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top