Page 3 of 3 FirstFirst 123
Results 21 to 26 of 26

Thread: VBA Filter before copying and pasting data

  1. #21
    New Member
    Join Date
    Jan 2019
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Filter before copying and pasting data

    Quote Originally Posted by Fluff View Post
    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 by MMDT19; May 9th, 2019 at 04:23 PM.

  2. #22
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    22,798
    Post Thanks / Like
    Mentioned
    381 Post(s)
    Tagged
    40 Thread(s)

    Default Re: VBA Filter before copying and pasting data

    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  3. #23
    New Member
    Join Date
    Jan 2019
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Re: VBA Filter before copying and pasting data

    Quote Originally Posted by Fluff View Post
    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 Fluff; May 10th, 2019 at 11:22 AM.

  4. #24
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    22,798
    Post Thanks / Like
    Mentioned
    381 Post(s)
    Tagged
    40 Thread(s)

    Default Re: VBA Filter before copying and pasting data

    Do you have any cells in col S that contain the this exact string "abc,xyz"
    Last edited by Fluff; May 10th, 2019 at 11:22 AM.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  5. #25
    New Member
    Join Date
    Jan 2019
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Filter before copying and pasting data

    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.

  6. #26
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    22,798
    Post Thanks / Like
    Mentioned
    381 Post(s)
    Tagged
    40 Thread(s)

    Default Re: VBA Filter before copying and pasting data

    Glad it's sorted & thanks for the feedback.
    I've anonymised the name
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •