Copying a filtered range xlValues only

Bagsy

Active Member
Joined
Feb 26, 2005
Messages
333
Office Version
365
Platform
Windows
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

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,332
Office Version
365
Platform
Windows
Try it like
Code:
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.
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,900
Office Version
2007
Platform
Windows
Code:
.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

Active Member
Joined
Feb 26, 2005
Messages
333
Office Version
365
Platform
Windows
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

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,332
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Dec 3, 2018
Messages
8,900
Office Version
2007
Platform
Windows
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

Well-known Member
Joined
Dec 3, 2018
Messages
8,900
Office Version
2007
Platform
Windows
Anyway, it seems to work for the OP and that is the important thing. :p
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,332
Office Version
365
Platform
Windows
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.
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,900
Office Version
2007
Platform
Windows
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 :(
 

Forum statistics

Threads
1,082,360
Messages
5,364,939
Members
400,815
Latest member
Joaquin Phoenix

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top