VBA Filter before copying and pasting data

MMDT19

New Member
Joined
Jan 18, 2019
Messages
12
If you only want to filter the one column use
Code:
With wb1.Sheets(12)
   If .AutoFilterMode Then .AutoFilterMode = False
   .Range("$A$1:$AM" & .Cells(.Rows.Count, "A").End(xlUp).Row).AutoFilter 19, "Employee"
   Set rngToCopy = .AutoFilter.Range
End With

I'd like to filter two. You helped me with this before.

With Wb1.Sheets(12)
If .AutoFilterMode Then .AutoFilterMode = False
.Range("$A$1:$AM" & .Cells(.Rows.Count, "A").End(xlUp).Row).AutoFilter Field:=7, _
Criteria1:="=*some*"
Set rngToCopy = .AutoFilter.Range
End With
rngToCopy.Copy
wb2.Sheets(2).Range("$A1").PasteSpecial xlValues

It worked perfectly fine, but now I'd like to add the second criteria in your message above.
 
Last edited:

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
39,164
Office Version
365
Platform
Windows
How about
Code:
With wb1.Sheets(12)
   If .AutoFilterMode Then .AutoFilterMode = False
   .Range("$A$1:$AM" & .Cells(.Rows.Count, "A").End(xlUp).Row).AutoFilter 7, "*some*"
   .Range("$A$1:$AM" & .Cells(.Rows.Count, "A").End(xlUp).Row).AutoFilter 19, "Employee"
   Set rngToCopy = .AutoFilter.Range
End With
 

MMDT19

New Member
Joined
Jan 18, 2019
Messages
12
How about
Code:
With wb1.Sheets(12)
   If .AutoFilterMode Then .AutoFilterMode = False
   .Range("$A$1:$AM" & .Cells(.Rows.Count, "A").End(xlUp).Row).AutoFilter 7, "*some*"
   .Range("$A$1:$AM" & .Cells(.Rows.Count, "A").End(xlUp).Row).AutoFilter 19, "Employee"
   Set rngToCopy = .AutoFilter.Range
End With
Thanks! This doesn't fail when executing or running the debugger. But it also doesn't copy the data. I'm not quite sure what's going wrong. This method was the first that I tried also.


Code:
 With Wb1.Sheets(12)   If .AutoFilterMode Then .AutoFilterMode = False
   .Range("$A$1:$AM" & .Cells(.Rows.Count, "A").End(xlUp).Row).AutoFilter 7, "*Employee*"
   .Range("$A$1:$AM" & .Cells(.Rows.Count, "A").End(xlUp).Row).AutoFilter 19, "abc,xyz"
   Set rngToCopy = .AutoFilter.Range
End With
        rngToCopy.Copy
        wb2.Sheets(2).Range("$A1").PasteSpecial xlValues
    End If
    ThisWorkbook.RefreshAll
[/QUOTE]
 
Last edited by a moderator:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
39,164
Office Version
365
Platform
Windows
Do you have any cells in col S that contain the this exact string "abc,xyz"
 
Last edited:

MMDT19

New Member
Joined
Jan 18, 2019
Messages
12
Yes, I found the error. Some way I was confused, it was column 38 instead of 7 for Employee. I see that you're a Moderator. Is it possible to alter our replies so that the name is removed. I typically anonymized it for privacy. I want to make sure it's not included but don't want to delete the thread in case it can help someone else.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
39,164
Office Version
365
Platform
Windows
Glad it's sorted & thanks for the feedback.
I've anonymised the name
 

Watch MrExcel Video

Forum statistics

Threads
1,096,446
Messages
5,450,494
Members
405,614
Latest member
SJ789

This Week's Hot Topics

Top