VBA Question - How To Autofilter on One Sheet With Single Criteria From Another Sheet?

[trikky]

Hi, Brains!

I have done lots of searching for the answer to this question online, and I have tried maybe six or seven different suggestions I found online, but none that I have tried seem to be work. Therefore, I appreciate any assistance I can get from the community here.

I have a multi-sheet workbook, with a data source sheet (updated through data connections to a Sharepoint igy file) and a presentation sheet. On the presentation sheet are three selection fields – name, month and – newly added –region. I already have it so that if the name is selected, only those months in which the name appears on my data source, will be shown in the month selection field, and if the month is selected first, only those names that appear within that specific month, will be shown in the selection field.

My goal is to have users pick a region on the presentation sheet, which then triggers the auto filter on the source sheet to filter based on that selection. For example, if someone were to pick "Australia"on the presentation sheet, only those rows with "Australia" in the target column of the data source sheet would be shown. From there, the existing contextual list for month and name take over, but of course, with available selections being impacted by the regionally filtered results on the data page .

The Data source sheet is named Data_CrossUp , with data appearing in columns A:X (data to be filtered is in column B). The Presentation sheet is named Cross Up Audit , with the selection to be made, appearing in a merged cell that covers G6:I6.

Ultimately, my question is – what VBA could I use to accomplish my goal, and on which sheet would I place it?

Thank you very much for your assistance.

 

[trikky]

https://www.dropbox.com/s/o5uanmud0dsdih3/Audit Sample.xlsm?dl=0 Sample file now hosted on dropbox

 

[JumboCactuar]

something like this?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myMonth As String


With Application
  .EnableEvents = False
  .ScreenUpdating = False
  


myMonth = Sheets("Cross Up Audit").Range("C6")


    If Target.Address = "$C$6" Then
        Sheets("Data_Crossup").ListObjects("Table_Crossup").Range.AutoFilter Field:=1, _
        Criteria1:=myMonth
    End If




  .EnableEvents = True
  .ScreenUpdating = True
End With




End Sub

 

[trikky]

Thanks, but no luck here.

In the VBA editor, I've attached it to both the presentation sheet and the data source sheet, as well as the ThisWorkbook object, and the Data_CrossUP page doesn't filter when I make a change on Cross Up Audit.

 

[JumboCactuar]

It worked for me, changing the month in C6 would filter the data sheet based on selection
If that is the intention or something else?

Code should just be placed on your Cross Up Audit sheet

 

[trikky]

Got it, thanks. Tried to edit my earlier message but got timed out by the system and had to do a new message.

Initially didn't work, but on investigation there was an old sub that I had tried earlier, that seems to have been getting in the way. Deleted that and it's running fine.

Now, as a followup question, if I have multiple Presentation sheets and multiple data sheets, what would be the best way to have this functionality for all of them? The cell references are the same, only the sheet names differ an each presentation sheet pairs up with a data sheet.

 

[JumboCactuar]

Pretty much just need to copy the sheet code across and edit the references