VBA - Find header based on variable and then copy data to other sheet

[Raaverok]

I am kind of new to VBA and with the help of Google I could not get this issue solved. The filtering with AutoFilter is no problem, but finding the correct header to filter based on the variable I could not get to work.

In my Excel file I have 2 sheets: Client and Overview.
Within the Client sheet I have all my clients as headers and destinations as rows. Below the clients I have indicated with an X if this destination is applicable to that client.
In my Overview sheet cell B1 I will fill in my client name, this value will be variable based on the users input.

I then want to filter the Client sheet based on my value in B1 and then filter on X.
In this way the page will be filtered on the destinations that have a X for that specific client. Then I want to copy the values in column A to B5 and below in the Overview sheet.

I have added 2 screenshots below as example:

Input page Client, in the 1st row I have to search my client name and then filter on X to get the correct destinations for this client:

Output Page Overview, Based on value in cell B1 I then need to copy the destinations from column A to B5 and below.

 

[vcoolio]

Hello Raaverok,

See if the following code placed in a standard module and assigned to a button does the task for you:-

Option Explicit
Sub Test()
    
    Dim wsC As Worksheet: Set wsC = Sheets("Client")
    Dim wsO As Worksheet: Set wsO = Sheets("Overview")
    Dim Crit As String: Crit = wsO.[B1].Value
    Dim FRange As Range: Set FRange = wsC.[A1].CurrentRegion
    Dim CId As String: CId = FRange.Find(Crit).Column
    Dim ClrRng As Range: Set ClrRng = wsO.Range("B5", wsO.Range("B" & wsO.Rows.Count).End(xlUp))
    
    Application.ScreenUpdating = False
        
        ClrRng.Clear
        
        With wsC.[A1].CurrentRegion
                .AutoFilter CId, "x"
                .Columns("A").Offset(1).Copy wsO.Range("B" & Rows.Count).End(3)(2)
                .AutoFilter
        End With
    
    Application.ScreenUpdating = True

End Sub

I hope that this helps.

Cheerio,
vcoolio.