Copy visible filtered range

Hi all

I am stuck trying to get my macro to copy only the filtered rows from 1 workbook to another.

Here is the code I have so far (may be bit scrappy as I am new to vba and untrained).

Please Login or Register  to view this content.

The problem is that it doesn't copy all filtered results only the first 2 filtered rows. I did originally have it working with copy/paste but I want to avoid using the clipboard if poss.

I've been starring at it for ages and just can't figure out why it won't copy the other rows.

It has something to do with the srcUCA range as this is only set to 2.

I've attached 2 sample workbooks. The Import book has a button on it to pull in the data from the Use Case Allocation book.

Anyone able to shed any light on this for me?

Thanks
Yxx

Some pointers.


You have set srcUCA as a range object

You need to include a line which says if it is true, otherwise you will get an error if there are no visible cells to copy

Please Login or Register  to view this content.

Your header is in row 2, but you are filtering in row 3

Criteria1:="<>"?
Does not seem to be a correct line.

Hi AB33

Some of the object settings are a hangover from my previous workings. I've tidied them up now.

Thanks for the snippet of code. I haven't put any error handling in yet. That is one of my next challenges along with opening dialogs to choose files instead.

The Criteria1 is looking for all non-blanks.

I have changed the row to filter on 2 now but still get same result.

I'm wondering if it the use of .SpecialCells(xlCellTypeVisible) that is wrong? If I step through I can see it filter out 6 rows but only copies the first 2.

Yxx

I have now tested that section and it works for me. You can remove the special cells from the line. It is a default assumption once you have Auto filter.

Please Login or Register  to view this content.

Some searching has revealed that SpecialCells returns a non-contiguous range hence why I am only getting the first 2.

I was hoping to avoid Copy and Paste. That was my original working method but I am trying to change it to using .Value to avoid the clipboard.

Thanks for your help.

Yxx

SpecialCells returns a non-contiguous range

yes, but that is more relevant if you have large data to filter(I think the limit is 20,000) visible cells

You do not have to copy and paste. I did it to show you the filer, copy and paste bits are correct.
Which line in your code shows the filtered cells are copied in to?

I think I have sussed it out.

More searching revealed I need to copy each Area of the filtered results.

Please Login or Register  to view this content.

Thanks for your pointers and your time.

Yxx