VBA Faster Way to copy filtered contents to a new sheet

LNG2013

Active Member
Joined
May 23, 2011
Messages
428
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
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,761
Office Version
365
Platform
Windows
How about
Code:
Sheets("Obj").copy after:=sheets(sheets.count)
ActiveSheet.Name="Filtered"
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,761
Office Version
365
Platform
Windows
Glad to help & thanks for the feedback
 

LNG2013

Active Member
Joined
May 23, 2011
Messages
428
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?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,761
Office Version
365
Platform
Windows
Ok, how about
Code:
    Sheets.Add.Name = "Filtered"
    Sheets("Obj").AutoFilter.Range.Copy Sheets("Filtered").Range("A1")
 

Watch MrExcel Video

Forum statistics

Threads
1,095,748
Messages
5,446,266
Members
405,392
Latest member
Steveoaktree1977

This Week's Hot Topics

Top