EXCEL TABLE filtered by dropdown list

Hi Lennon,

You may try something like this.

Place the following code on "main' Sheet Module.

Code on main Sheet Module:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Target.Address(0, 0) = "B2" Then
    Application.EnableEvents = False
    ActiveSheet.AutoFilterMode = False
    If Target <> "" Then
        Range("B5").AutoFilter field:=1, Criteria1:=Target.Value
    Else
        Range("B5").AutoFilter
    End If
    Application.EnableEvents = True
End If
End Sub

Select allOpen in new window

Then place the following code on a Standard Module like Module1 and assign this macro to the button called Run Macro on main Sheet.

Code on Moduel1:

Sub CopyData()
Dim wsData      As Worksheet
Dim wsResult    As Worksheet
Dim lr          As Long
Dim cnt         As Long

Application.ScreenUpdating = False

Set wsData = Worksheets("main")
Set wsResult = Worksheets("results")
wsResult.Range("A1").CurrentRegion.Offset(1).Clear

lr = wsData.Cells(Rows.Count, "B").End(xlUp).Row

If wsData.Range("B5:B" & lr).SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
    cnt = wsData.Range("E6:E" & lr).SpecialCells(xlCellTypeVisible).Cells.Count
    If Application.CountA(wsData.Range("E6:E" & lr).SpecialCells(xlCellTypeVisible)) = cnt Then
        wsData.Range("B6:E" & lr).Copy wsResult.Range("A" & Rows.Count).End(3)(2)
    Else
        MsgBox "Please fill all the cells with Yes/No in 'In inventory' column first and then try again...", vbExclamation
        Exit Sub
    End If
End If

wsResult.Select
Application.ScreenUpdating = True
MsgBox "Data has been copied to the Results Sheet.", vbInformation
End Sub

Select allOpen in new window

As per the above codes, once you select a model from the dropdown list in B2, the data will be filtered accordingly.

And once you fill the column E in the filtered data with either Yes or No and click the button Run Macro, the data will be transferred to the Results Sheet.

work_deck.xlsm

Power BI to the rescue:

- Add a sheet named "User Choice" (hide it later), insert a table (SelectedFilter) with a single row where you'll forward the dropdown's value.

- Update you data to a table (Input)

- Create a query (connection only) that will read the SelectedFilter table.

- Create a query that will read the input table, and combine it with the SelectedFilter query (this effectively join the 2 queries). Load it into the reult sheet.

- Done !

Just click the Update button in the ribbon's data tab.
(no VBA involved).

See the attached sample.work_deck.xlsx

Please test the attached with the tweaked code.

The code underneath the button called "Run Macro" will set the visible status of the Results Sheet to xlSheetVeryHidden.

work_deck v2.xlsm

Did you test the latest file I uploaded with the tweaked code?

It does what you are asking i.e. once you click the button, it sets the hidden status of the Results sheet to xlSheetVeryHidden programmatically if it is visible. And once the Results sheet is hidden, the Run Macro button still works without any error.

What I am missing here?

Please follow these steps to change the password...

1. Open your file.
2. Select main Sheet.
3. Go to Review Tab in the ribbon and click on Unprotect Sheet under Protect group.
4. Provide the existing password sktneer to unprotect the main sheet.
5. Once you unprotect the sheet, the Unprotect Sheet button will be changed to Protect Sheet on the Review Tab.
6. Click the Protect Sheet button and provide the New Password, click OK and reconfirm the New Password when prompted.
7. So now you have set the New Password Manually.
8. Now open VB Editor by pressing Alt+F11.
9. Double click on Module1 in the Project Explorer on VB Editor.
10. And replace the sktneer part with the New Password which you just set manually in the above steps on the first line of code on Module1 i.e. replace sktneer in the line Public Const PW As String = "sktneer" with your New Password.

So if you manually set the new password to say Lennon, the existing first line of code on Module1 should look like this...

Public Const PW As String = "Lennon"

Refer to the screenshot below to know where exactly you need to change the password in the code.

You're welcome Lennon!