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
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
Code:
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
