Hi
I trying to write VBA to use advanced filter to get some data from one table to another. I have this code..
Sheets("Data").ListObjects(1).Range.AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Criteria").Range("A1:A2"), _
CopyToRange:=Sheets("Query").ListObjects(1).HeaderRowRange, Unique:=False
But I can't get it to work. Gives me Run time error 1004, AdvancedFilter method of range class failed.
Would someone be able to show me where I'm going wrong.
Many thansk
Max
Welcome to the forum
Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Please see sample file attached. Clicking the button should run the macro and filter the data.
I don't think you can do that when the destination is a table (it doesn't seem to work manually either). I'd suggest you make the destination a table after you do the filter.
Everyone who confuses correlation and causation ends up dead.
Thanks Rory.
Sadly that's what I thought may be the case.
Trouble with that though is that I loose the ability to have formulas on the rest of the sheet based on the table or indeed add further calculated columns to the table.
I thought about making the query destination a range and then copying the data from that range to the table but that does seem like a long way round.
Max
If you need the table to remain, filter the original data in place, then copy and paste to the table.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks