Results 1 to 8 of 8

VBA Advanced Filter

This is a discussion on VBA Advanced Filter within the Excel Questions forums, part of the Question Forums category; Hi, could someone please help me with this code, i am trying to copy visible cells to another sheet, but ...

  1. #1
    Board Regular
    Join Date
    Nov 2008
    Location
    Sydney
    Posts
    636

    Default VBA Advanced Filter

    Hi, could someone please help me with this code, i am trying to copy visible cells to another sheet, but the code fails in line .offset

    Code:
     
     
    With shttest
    .Range("Data").AdvancedFilter Action:=xlFilterCopy, 
    CriteriaRange:=Range("Criteria")
            .Offset(1).Resize(.Rows.Count - 1, .Columns.Count).SpecialCells (xlCellTypeVisible), _
            CopyToRange:=shtRec.Range("A1")
        End With

  2. #2
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    26,001

    Default Re: VBA Advanced Filter

    Untested, but try:

    With shttest.Range("Data")
        .AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("Criteria")
        .Offset(1).Resize(.Rows.Count - 1, .Columns.Count).SpecialCells (xlCellTypeVisible), _
            CopyToRange:=shtRec.Range("A1")
    End With


    The code will still error if there are no visible cells after the filter.
    Hope this helps, good luck.
    Peter

    - Read: Posting Guidelines, Forum Rules & FAQs
    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste (To test: Test Here)
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker

  3. #3
    Board Regular
    Join Date
    Nov 2008
    Location
    Sydney
    Posts
    636

    Default Re: VBA Advanced Filter

    Quote Originally Posted by Peter_SSs View Post
    Untested, but try:

    With shttest.Range("Data")
    .AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("Criteria")
    .Offset(1).Resize(.Rows.Count - 1, .Columns.Count).SpecialCells (xlCellTypeVisible), _
    CopyToRange:=shtRec.Range("A1")
    End With


    The code will still error if there are no visible cells after the filter.
    Peter, i get an error "Named Argument not found"
    CopyToRange:=


  4. #4
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    26,001

    Default Re: VBA Advanced Filter

    Quote Originally Posted by Isabella View Post
    Peter, i get an error "Named Argument not found"
    CopyToRange:=
    Yes, that's usually the result when you don't test, and don't read carefully enough.

    If the code below does not do what you want, perhaps you better give us the rest of your code (so we know just what the variables you are using are) and explain exactly what you are trying to do.

    With shttest.Range("Data")
        .AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("Criteria"), _
            CopyToRange:=shtRec.Range("A1")
    End With
    shtRec.Rows(1).Delete ' Not sure if you want this line or not??
    Hope this helps, good luck.
    Peter

    - Read: Posting Guidelines, Forum Rules & FAQs
    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste (To test: Test Here)
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker

  5. #5
    Board Regular
    Join Date
    Nov 2008
    Location
    Sydney
    Posts
    636

    Default Re: VBA Advanced Filter

    Quote Originally Posted by Peter_SSs View Post
    Yes, that's usually the result when you don't test, and don't read carefully enough.

    If the code below does not do what you want, perhaps you better give us the rest of your code (so we know just what the variables you are using are) and explain exactly what you are trying to do.

    With shttest.Range("Data")
    .AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("Criteria"), _
    CopyToRange:=shtRec.Range("A1")
    End With
    shtRec.Rows(1).Delete ' Not sure if you want this line or not??
    Peter this is the rest of my code, i am simply copying data that is in my criteria to another sheet


    Code:
     
    Sub Test()
    
    Dim shtTestAs Worksheet
    Dim shtRec As Worksheet
    
     
    Set shttest = Sheets("test")
    Set shtRec = Sheets("Rec")
     
    With shtWorkings.Range("Data")
        .AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("Criteria")
        .Offset(1).Resize(.Rows.Count - 1, .Columns.Count).SpecialCells (xlCellTypeVisible), _
        CopyToRange = shtRec.Range("A7")
    End With
     
    End Sub

  6. #6
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    26,001

    Default Re: VBA Advanced Filter

    Quote Originally Posted by Isabella View Post
    Peter this is the rest of my code, i am simply copying data that is in my criteria to another sheet


    Code:
     
    Sub Test()
    
    Dim shtTestAs Worksheet
    Dim shtRec As Worksheet
    
     
    Set shttest = Sheets("test")
    Set shtRec = Sheets("Rec")
     
    With shtWorkings.Range("Data")
        .AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("Criteria")
        .Offset(1).Resize(.Rows.Count - 1, .Columns.Count).SpecialCells (xlCellTypeVisible), _
        CopyToRange = shtRec.Range("A7")
    End With
     
    End Sub
    So, do you now have the code doing what you want?

    If not ..

    a) Why do you have ...

    Dim shtRec As Worksheet

    Set shttest = Sheets("test")

    ... but then not use shttest in your code?

    b) Why do you have ...

    With shtWorkings.Range("Data")

    ... without 'Dim'ing or defining shtWorkings?

    c) Did you try my suggested code?

    d) Please confirm the name of the sheet your actual data is on and presumably contains the named ranges "Data" and "Criteria"?

    e) Please confirm the name of the sheet you want your results to appear on?
    Hope this helps, good luck.
    Peter

    - Read: Posting Guidelines, Forum Rules & FAQs
    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste (To test: Test Here)
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker

  7. #7
    Board Regular
    Join Date
    Nov 2008
    Location
    Sydney
    Posts
    636

    Default Re: VBA Advanced Filter

    Peter your solution did not work, this is the full code, my data is in sheet test and i want the result to be copied to sheet Rec. When i run your solution the code only copies the header and the data in first column, everything else is missing.

    Code:
     
    Sub test()
     
    Dim shttest As Worksheet
    Dim shtRec As Worksheet
     
    Set shttest = Sheets("test")
    Set shtRec = Sheets("Rec")
     
    With shttest.Range("Data")
        .AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("Criteria"), _
            CopyToRange:=shtRec.Range("A7")
    End With
     
    End Sub


    Quote Originally Posted by Peter_SSs View Post
    So, do you now have the code doing what you want?

    If not ..

    a) Why do you have ...

    Dim shtRec As Worksheet

    Set shttest = Sheets("test")

    ... but then not use shttest in your code?

    b) Why do you have ...

    With shtWorkings.Range("Data")

    ... without 'Dim'ing or defining shtWorkings?

    c) Did you try my suggested code?

    d) Please confirm the name of the sheet your actual data is on and presumably contains the named ranges "Data" and "Criteria"?

    e) Please confirm the name of the sheet you want your results to appear on?

  8. #8
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    26,001

    Default Re: VBA Advanced Filter

    Quote Originally Posted by Isabella View Post
    ... this is the full code, my data is in sheet test and i want the result to be copied to sheet Rec ....

    Code:
     
    Sub test()
     
    Dim shttest As Worksheet
    Dim shtRec As Worksheet
     
    Set shttest = Sheets("test")
    Set shtRec = Sheets("Rec")
     
    With shttest.Range("Data")
        .AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("Criteria"), _
            CopyToRange:=shtRec.Range("A7")
    End With
     
    End Sub
    This code appears to work for me.

    Here is my "test" sheet. The named range 'Data' is the green range. Te named range 'Criteria' is the blue range.

    test

     ABCDEFG
    1H1H2H3H4 H4 
    2awsd d 
    3ssas   
    4we w   
    5adwe   
    6s ed   
    7       


    Excel tables to the web >> Excel Jeanie HTML 4



    After running the code ...

    Sub test()
        Dim shttest As Worksheet
        Dim shtRec As Worksheet

        Set shttest = Sheets("test")
        Set shtRec = Sheets("Rec")
        
        With shttest.Range("Data")
            .AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("Criteria"), _
                CopyToRange:=shtRec.Range("A7")
        End With
    End Sub


    ... the "Rec" sheet looks as below. Is that what you would expect?


    Rec

     ABCDE
    6     
    7H1H2H3H4 
    8awsd 
    9s ed 
    10     


    Excel tables to the web >> Excel Jeanie HTML 4
    Hope this helps, good luck.
    Peter

    - Read: Posting Guidelines, Forum Rules & FAQs
    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste (To test: Test Here)
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker

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
  •  


DMCA.com