One of the more common tasks done with VBA is copying filtered rows into a new sheet.
I noticed that most of the Excel users I encounter do this by recording a macro that captures the following movements:
- Click the top-left corner of the table
- Press Ctrl+Shift+Down Arrow
- Press Ctrl+Shift+Right Arrow
- Press Ctrl+C to Copy
- Start a new Workbook and then Paste

.
This usually leaves them with a macro that looks something like code seen below. Note the Selection.End(xlDown) and Selection.End(xlToRight). This is really the hard way to go; plus this could possibly be dangerous if the data in the table is not reliably filled in completely.

.
A much easier and safer way is to use the built-in AutoFilter.Range object of the worksheet.
This code does the same action more cleanly.
ActiveSheet.AutoFilter.Range.Copy
Workbooks.Add.Worksheets(1).Paste
.
Now you know.
.
Like this tip? You'll find lots more of these gems in this awesome book.
Yes…there's a redneck on the front cover, but don't let that deter you. The author is extraordinarily smart and handsome.
Click the book see.



