VBA Search a table header row filter & copy used range

[Bagsy]

I hope somebody can help me a bit please.
I am attempting to put some code together to search for a name in a header row of a table, filter that column non blank cells. But it needs to filter the whole table.
I have the code working up to the point where it searches the header rows and finds the name
But I am struggling to get filter and copy to work.
I need the whole table filtered because I am eventually trying to achieve as follows
• Search for a name in a table in Sheets("Collated Data")
• Filter & copy the used range in found column (this is day or ½ day holiday)
• Paste onto an employee holiday allocation sheet
• Come back to the table in Sheets("Collated Data")
• Copy column D used range (these are the holiday dates)
• Paste this on the same holiday allocation sheet
At the moment I cannot get passed the filter and copy stage to work on the rest of the code
Any help is very much appreciated

Sub Addholidays()
Dim WB As Workbook
Dim CurrentSheet As Worksheet
Set CurrentSheet = ActiveSheet
Dim Sh As Worksheet
Dim Locate As Range
Dim Name As String
'store Name value???
Dim Found As Boolean

Ans = MsgBox("Have you selected the correct employee name", vbYesNo)
If Ans = vbNo Then Exit Sub

Application.ScreenUpdating = False

'On Error GoTo ErrorHandler

Sheets("Planner").Select
    Name = ActiveCell.Value
        Sheets("Collated Data").Visible = True
            Sheets("Collated Data").Select
        Range("D4").Select 'select the first line of data in range D4:BP4
    Found = False ' Set Boolean variable "found" to false.
Do Until IsEmpty(ActiveCell) ' Set Do loop to stop at empty cell.

If ActiveCell.Value = Name Then ' Check active cell for search value.
    Found = True
        Exit Do
    End If
ActiveCell.Offset(0, 1).Select ' Step over 1 column from present location.
Loop
 
If Found = True Then ' Checked for found.

'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx below this line not working yet
Range("Name").AutoFilter.Column , Criteria1:="<>"  'filter the whole table from found name column, non-blank cells. Table range (D4:BP370 including header row)
Range("Name").Copy.UsedRange  'copy all non blank cells in filtered used range below found name
End If

 

[Fluff]

That line is copying the first column of the autofiltered range (less the header row) and it subtracts 3 from the column number because the data starts in the 4th column.

You will need to start a new thread for your new request.