Copying a filtered range xlValues only

[Bagsy]

I wonder if anybody can help, I have put some code together with a great deal of help from Fluff on this site.
I have now moved onto the next stage, which I have working but not exactly as I need.
I have the code copying and pasting to variable sheet name but I have two problems
1. Its copying and pasting formats & I need xlValues only
2. Its copying the filtered data OK but is also copying the next blank cell after the filtered data
Any help is appreciated

Sub Addholidays()
Dim WB As Workbook
Dim CurrentSheet As Worksheet
Set CurrentSheet = ActiveSheet
Dim Sh As Worksheet
Dim Locate As Range
Dim Nme As String
Dim Fnd As Range

Application.ScreenUpdating = False
Sheets("Planner").Select
    Nme = ActiveCell.Value
    Ans = MsgBox("Have you selected the correct employee name " & ActiveCell.Value, vbYesNo)
    If Ans = vbNo Then Exit Sub

'On Error GoTo ErrorHandler

Sheets("Planner").Select
    Nme = ActiveCell.Value
    Sheets("Collated Data").Visible = True
    Sheets("Collated Data").Select
    
With Sheets("Collated Data")
    If .AutoFilterMode Then .AutoFilterMode = False
    Set Fnd = .Range("D4:BP4").Find(Nme, , xlValues, xlWhole, , , False, , False)
    .Range("D4:BP4").AutoFilter Fnd.Column - 3, "<>" ' this is filtering 3 rows down (Header Row) from row1
    .AutoFilter.Range.Offset(1).Columns(Fnd.Column - 3).Copy Sheets(Nme).Range("D26")
    .AutoFilter.Range.Offset(1).Columns(1).Copy Sheets(Nme).Range("C26")
    .AutoFilterMode = False
              
              
Sheets("Collated Data").Visible = False
Sheets("Planner").Select
Application.ScreenUpdating = True

Exit Sub
ErrorHandler: MsgBox ("Sheet for this employee has not been created."), , "Check Sheet is named correctly"
End With
End Sub

Here is what I have tried

'.AutoFilter.Range.Offset(1).Columns(Fnd.Column - 3).Copy Sheets(Nme).Range.xlValues("D26")
'.AutoFilter.Range.Offset(1).Columns(1).Copy Sheets(Nme).Range.xlValues("C26")
'
'.AutoFilter.Range.Offset(1).Columns(Fnd.Column - 3).Copy Sheets(Nme).Range("D26").xlValues
'.AutoFilter.Range.Offset(1).Columns(1).Copy Sheets(Nme).Range("C26").xlValues
'
'.AutoFilter.Range.Offset(1).Columns(Fnd.Column - 3).Copy.xlValues Sheets(Nme).Range("D26")
'.AutoFilter.Range.Offset(1).Columns(1).Copy.xlValues Sheets(Nme).Range("C26")

 

[Fluff]

Try it like

Sub Addholidays()
Dim WB As Workbook
Dim CurrentSheet As Worksheet
Set CurrentSheet = ActiveSheet
Dim Sh As Worksheet
Dim Locate As Range
Dim Nme As String
Dim Fnd As Range

Application.ScreenUpdating = False
Sheets("Planner").Select
    Nme = ActiveCell.Value
    Ans = MsgBox("Have you selected the correct employee name " & [COLOR=#0000ff]Nme[/COLOR], vbYesNo)
    If Ans = vbNo Then Exit Sub

'On Error GoTo ErrorHandler

[COLOR=#ff0000]Sheets("Planner").Select[/COLOR]
   [COLOR=#ff0000] Nme = ActiveCell.Value[/COLOR]
    [COLOR=#ff0000]Sheets("Collated Data").Visible = True
    Sheets("Collated Data").Select[/COLOR]
    
With Sheets("Collated Data")
    If .AutoFilterMode Then .AutoFilterMode = False
    Set Fnd = .Range("D4:BP4").Find(Nme, , xlValues, xlWhole, , , False, , False)
    .Range("D4:BP4").AutoFilter Fnd.Column - 3, "<>" ' this is filtering 3 rows down (Header Row) from row1
[COLOR=#0000ff]    .AutoFilter.Range.Offset(1).Columns(Fnd.Column - 3).Copy
    Sheets(Nme).Range("D26").PasteSpecial xlPasteValues
    .AutoFilter.Range.Offset(1).Columns(1).Copy
    Sheets(Nme).Range("C26").PasteSpecial xlPasteValues[/COLOR]
    .AutoFilterMode = False
[COLOR=#0000ff]End With[/COLOR]
  
              
[COLOR=#ff0000]Sheets("Collated Data").Visible = False
Sheets("Planner").Select[/COLOR]
Application.ScreenUpdating = True

Exit Sub
ErrorHandler: MsgBox ("Sheet for this employee has not been created."), , "Check Sheet is named correctly"
End Sub

Changes are in blue & the parts in red are completely redundant & can be deleted.

 

[DanteAmor]

.Range("D4:BP4").AutoFilter Fnd.Column - 3, "<>" ' this is filtering 3 rows down (Header Row) from row1

What the previous line does is filter by the column number found -3, for example, if the column found is J, then column (J) - 3 = column (G). Filtering is done in column G.

What do you really need to filter?
Where are your headings?
And which columns do you want to copy?

 

[Bagsy]

Thanks you so much for your help Fluff, all that work and it just boils down to a few lines of code. Amazing
Here is my completed code in case it can help anybody else

Sub Addholidays()
Dim WB As Workbook
Dim CurrentSheet As Worksheet
Set CurrentSheet = ActiveSheet
Dim Sh As Worksheet
Dim Locate As Range
Dim Nme As String
Dim Fnd As Range

Application.ScreenUpdating = False
Sheets("Planner").Select
    Nme = ActiveCell.Value
    Ans = MsgBox("Have you selected the correct employee name " & Nme, vbYesNo)
    If Ans = vbNo Then Exit Sub

On Error GoTo ErrorHandler
    
With Sheets("Collated Data")
    If .AutoFilterMode Then .AutoFilterMode = False
    Set Fnd = .Range("D4:BP4").Find(Nme, , xlValues, xlWhole, , , False, , False)
    .Range("D4:BP4").AutoFilter Fnd.Column - 3, "<>" ' this is filtering 3 rows down (Header Row) from row1
    .AutoFilter.Range.Offset(1).Columns(Fnd.Column - 3).Copy
    Sheets(Nme).Range("D26").PasteSpecial xlPasteValues
    .AutoFilter.Range.Offset(1).Columns(1).Copy
    Sheets(Nme).Range("C26").PasteSpecial xlPasteValues
    .AutoFilterMode = False
End With
  
Application.ScreenUpdating = True
MsgBox "Holiday sheet has been updated for " & Nme
Exit Sub
ErrorHandler: MsgBox ("Sheet for this employee has not been created."), , "Check Sheet is named correctly"
End Sub

 

[Fluff]

Glad to help & thanks for the feedback.
@DanteAmor
Because the filter is applied starting at col D, you need to subtract 3 as col D is field 1, col E is field 2 etc.

 

[DanteAmor]

Glad to help & thanks for the feedback.
@DanteAmor
Because the filter is applied starting at col D, you need to subtract 3 as col D is field 1, col E is field 2 etc.

Hi fluff, That is clear to me, but I do not know if it is clear to the OP, since in the code it has this comment:

​this is filtering 3 rows down (Header Row) from row1

 

[DanteAmor]

Anyway, it seems to work for the OP and that is the important thing.

 

[Fluff]

Hi fluff, That is clear to me, but I do not know if it is clear to the OP, since in the code it has this comment:

I hadn't noticed the comment in the code, but I may not have made myself clear.
If the found col is J, then col J will get filtered not col G, because the autofilter starts in col D rather than col A.

 

[DanteAmor]

I hadn't noticed the comment in the code, but I may not have made myself clear.
If the found col is J, then col J will get filtered not col G, because the autofilter starts in col D rather than col A.

Totally agree, my mistake