using range.advancedfilter to copy data into a list object

Good morning, all.

I am working with Range.AdvancedFilter to get a unique set of values FROM a listObject and then insert them INTO another list object.

The destination listObject is created using VBA and is initially only a header and one row below that.

I can use the code below to get a unique set of values FROM Categories[Agency] but when i copy them TO totalPaidToAgency[Agency] only the first element in the range is inserted into totalPaidToAgency. The others are there but are not part of totalPaidToAgency. How can I use AdvancedFilter with xlFilterCopy to insert all of the elements into totalPaidToAgency?

Thank you in advance!

Please Login or Register  to view this content.

Originally Posted by Norie

Why not create the 2nd listobject from the copied range from the advanced filter?

@Norie, that is a good idea. I didnt think of it that way. I will mess around with it and see if I can get that to work

This works with one exception...

I insert my AdvancedFilter copy data at A3. A1 is a text (presentation purposes only) title of my table. A2 is my header row.

Because I inserted my data at A3, when I use Range(A3).CurrentRegion, it pulls A1 and A2 into current region. Therefore, A1 becomes my header row instead of A2.

Is there a way to do something like a Range.CurrentRegion + 1? That obviously doesnt work but...something like that?

Originally Posted by Norie

Thought that might be a problem, there's a few ways to deal with it, kind of depends on the layout of the sheet.

Could you upload a sample workbook so I can see how your worksheet is set up?

Here you go. I have taken out all of the actual data because is it not something I can publish. If you need more sample data than what I have provided, please let me know.

Also, thanks again for your continued help.

I have been trying to get this to work but, I am getting an error of "object required" at endRange which I am guessing means that it cant convert Rows.Count to something that can be added to a String. Sorry, I know my code lingo is out of whack here...it has been a long time since I seriously coded.

Please Login or Register  to view this content.

Originally Posted by mtnbiker98

I have been trying to get this to work but, I am getting an error of "object required" at endRange which I am guessing means that it cant convert Rows.Count to something that can be added to a String. Sorry, I know my code lingo is out of whack here...it has been a long time since I seriously coded.

Please Login or Register  to view this content.

Figured this one out. endRange is not an object so I cant use "Set"

Originally Posted by Norie

Thought that might be a problem, there's a few ways to deal with it, kind of depends on the layout of the sheet.

Could you upload a sample workbook so I can see how your worksheet is set up?

I figured most of it out with the Code below. It builds the table with the correct headers now. However, when I pull CurrentRegion.Count I get 206 where I should get something like 103 to 105. I start the whole sub by clearing all cells with ActiveSheet.Cells.Clear so, there isnt any other data in there. Any ideas?

Got it. Thanks! I will do so in the future.