Copying a filtered range xlValues only

Bagsy

Active Member
Joined
Feb 26, 2005
Messages
467
Office Version
  1. 365
Platform
  1. 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")
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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:
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Anyway, it seems to work for the OP and that is the important thing. :p
 
Upvote 0
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:
Upvote 0
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 :(
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top