Copying fiIltered rows

I've searched on the forums but I've yet to find any solution that I understood or could use. So I will try to explain what I want to accomplish and what I've tried.

So I have a list that I will filter several times, for each of those times I need to copy the content that is shown to a new worksheet. Since I'm a beginner I tried to use the macro recorder and then I ended up with something like this.

Please Login or Register  to view this content.

Here Cells(StartRowToCopyFrom, StartColumnToCopyFrom) is the top left corner of the area I want to copy from and Cells(EndRowToCopyFrom, EndCollumnToCopyFrom) is the bottom right corner of the area I want to copy from.

I googled the error code I got when trying this "Error 1004", which lead me to belive that you can only copy like this if you do it manually and when using macros it wont work, since the list is filtered.

So Is there someone that can help me/put me in the right direction. I've seen examples using .SpecialCells .(xlCellTypeVisible).Copy and ).End(XlUp).Row But I've not been able to get it to work, and frankly I've not understood the examples I've read either.

Hello sidesprang,

I only speak Excel 2007, but I bet this workaround will work for your 2010:

If you are copying your filtered data from one column to another, on the same sheet, the solution is simple. Lets say your filtered data is in Sheet 1, Column B, starting in B1. After you've filtered it, choose the first cell you're trying to move your data to. Lets call it C1. In C1, you simply type

Please Login or Register  to view this content.

and drag it down. Unfilter everything, and only your filtered results should have copied over to Column C.

If you are copying your filtered data onto another sheet (Sheet 2), the solution simply requires that you follow the steps in the previous paragraph. Only this time, type that equals B1 formula into an unused column on Sheet 1, and drag it down. After you have done that and unfiltered, cut-and-paste the results onto the appropriate column on Sheet 2. The extra step of cutting and pasting is necessary, because you can't reference filtered data from another sheet (at least in 2007).