Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cut and Paste after Auto FIlter
I've created a database with 10 columns and about 500
lines. I autofilter based on a value in column 10 and want to copy and paste the data (do not want the header copied). The autofilter first selected ciriteria is line 114 (this can change from month to month). I tried to do a copy end down end right using recording a new maco, unfortunately because one of the columns in the first line of the database is empty, I am not copying all the data that I need. The end right is only going to open column in the second line of the database (which is NOT included in the autofilter). How do I modify the macro code to work around this issue? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cut and Paste after Auto FIlter
Using your approach, once your top left data cell is selected you could go
up to the header line, end-right, down one row, and end-down and there you should be at the bottom right of your filtered data, assuming the far right column has no missing values. However, this is not fool-proof, and the macro recorder is by no means the best approach to coding this. If I had more time right now I would look into it more. See if this works for you. Range(Selection, Selection.End(xlUp)).Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Bruce Cooley "David Dean" wrote in message ... : I've created a database with 10 columns and about 500 : lines. I autofilter based on a value in column 10 and : want to copy and paste the data (do not want the header : copied). The autofilter first selected ciriteria is line : 114 (this can change from month to month). I tried to do : a copy end down end right using recording a new maco, : unfortunately because one of the columns in the first line : of the database is empty, I am not copying all the data : that I need. The end right is only going to open column : in the second line of the database (which is NOT included : in the autofilter). How do I modify the macro code to : work around this issue? : : Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cut and Paste after Auto FIlter
Dim rng as Range
set rng = Activesheet.Autofilter.Range set rng = rng.offset(1,0).Resize(rng.rows.count-1) rng.copy Destination:=Worksheets("Sheet2").Range("A1") -- Regards, Tom Ogilvy "David Dean" wrote in message ... I've created a database with 10 columns and about 500 lines. I autofilter based on a value in column 10 and want to copy and paste the data (do not want the header copied). The autofilter first selected ciriteria is line 114 (this can change from month to month). I tried to do a copy end down end right using recording a new maco, unfortunately because one of the columns in the first line of the database is empty, I am not copying all the data that I need. The end right is only going to open column in the second line of the database (which is NOT included in the autofilter). How do I modify the macro code to work around this issue? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
DataFilterAuto Filter in excel 2007? | New Users to Excel | |||
Excel 2007 Auto Filter Filter | Excel Discussion (Misc queries) | |||
Auto-Filter copy/paste problem | Excel Discussion (Misc queries) | |||
how to auto "copy & paste" (or filter) from a Master spreadsheet? | Excel Worksheet Functions | |||
Excel auto filter doesn't recoginize case - won't filter AA from A | Excel Discussion (Misc queries) |