VBA Faster Way to copy filtered contents to a new sheet

LNG2013

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

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
How about
Code:
Sheets("Obj").copy after:=sheets(sheets.count)
ActiveSheet.Name="Filtered"
 
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0
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?
 
Upvote 0
Ok, how about
Code:
    Sheets.Add.Name = "Filtered"
    Sheets("Obj").AutoFilter.Range.Copy Sheets("Filtered").Range("A1")
 
Upvote 0

Forum statistics

Threads
1,213,511
Messages
6,114,054
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top