Results 1 to 6 of 6

Thread: VBA Faster Way to copy filtered contents to a new sheet
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    May 2011
    Posts
    422
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question VBA Faster Way to copy filtered contents to a new sheet

    Hello,

    I have been told to many Select and copy and paste commands can slow down my code.

    What would be a faster way of doing this? - It is copying the filtered results to a new Sheet called filtered.

    Code:
    
    Sub Filtered()
    '
    '~~~~ This macro adds the filtered sheet to begin the report process
    '
    
    Sheets.Add.Name = "Filtered"
    
        Sheets("Obj").Select
        Cells.Select
        Selection.Copy
        Sheets("Filtered").Select
        Cells.Select
        ActiveSheet.Paste
    End Sub
    Running Excel 2010

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,126
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: VBA Faster Way to copy filtered contents to a new sheet

    How about
    Code:
    Sheets("Obj").copy after:=sheets(sheets.count)
    ActiveSheet.Name="Filtered"
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular
    Join Date
    May 2011
    Posts
    422
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Faster Way to copy filtered contents to a new sheet

    That works thank you so much!
    Running Excel 2010

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,126
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: VBA Faster Way to copy filtered contents to a new sheet

    Glad to help & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    Board Regular
    Join Date
    May 2011
    Posts
    422
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Faster Way to copy filtered contents to a new sheet

    Hello,

    Thank you again for the code above. I have experienced an unusual side effect though which it looks like instead of copying the filtered range to a new sheet name filtered, it copied everything and mad it filtered on the new sheet. This means it brought with it alot of data that is slowing down other parts of the report.
    Is there a faster way to copy just the filtered contents of the sheet Obj to a new sheet called Filtered?
    Running Excel 2010

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,126
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: VBA Faster Way to copy filtered contents to a new sheet

    Ok, how about
    Code:
        Sheets.Add.Name = "Filtered"
        Sheets("Obj").AutoFilter.Range.Copy Sheets("Filtered").Range("A1")
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

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
  •