I need to do this same thing (parse one file into multiple files). I copied the first macro and tried to use it in with my file, but get an error on -- Columns("C:C").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("AA1"), Unique:=True -- the error is "The extract range has a missing or illegal field name." Meaningful error to you?? Any idea how I can solve this? Thank you.
Moderator's Note: Moved from this thread, as per forum rules against taking over other people's threads.
Last edited by ktalamantez; 08-24-2013 at 08:59 PM. Reason: Moved to its own thread as per hijacking rules. Please read the Forum Rules in the menu bar above. Thanks.
1) Move the DATA you already have in column AA over to column W, or some other empty column. The macro is using column AA to create the unique list of Directors.
2) In case you run the macro multiple times, probably a good idea to erase the named ranges the Advanced Filter creates.
3) I've updated the code to no longer "select" sheets, unneeded.
Please Login or Register to view this content.
Last edited by JBeaucaire; 08-25-2013 at 02:17 AM.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
shorter, faster, Col AA doesn't matterPlease Login or Register to view this content.
@Kalak - no disrespect, your code is shorter because there is no error checking. And faster? On a data set with 5000 rows, for instance, your code has to loop 5000 times, while the approach I always suggest only loops the number of unique values in the criteria column. So if only 8 names, then that's 8 loops and done regardless how long that data is, even tens of thousands of rows.
Anyway, your code looks good, add some error checking so it can be run twice in succession on the same data and that would be ready for prime-time.
Cheers.
hi,
am also looking the same solution, one file to multiple files.
if you got any good result, please give to me also...
thanks
Josh
@Jerry B
Thanks for the considered response.
Please read the last part of these comments, even if not the rest.
1. Errors. Depends what errors are to be checked. In this type of problem the likely one is trying to re-create an already-existing worksheet. What happens in such case depends on the user, e.g. replace existing worksheet, or append data at the bottom, or leave the existing sheet as it is, or create a new sheet with an easily-identifiable similar name, or ...
Allowing for any of these is easily included in the code I posted with little increase in length. But I'd need to know which is wanted. Do you know which one is wanted? So what does your error checking actually check?
Easy and short enough to give a message box saying "Sheet already exists" or similar, but since the VBA tells you this anyway, what additional purpose is there in including it? It's errors that don't give error messages, such as in your own code, that concern me a lot more. See below.
2. Loops can be either explicit, or implicit as with the filter type approaches. Whichever way, all rows for this type of problem need be checked, so seems should come down to which approach is more efficient.
I used shorter and faster as efficiency criteria. Do you prefer others?
Faster. I wouldn't make such claim without checking it first.
So, generated a bit of test data with the code below. You can easily check anything I say here with this, or your own test data.
With the 5000 rows and 8 unique names you refer to, my code took 0.14 secs on an average spec notebook. Your code took just about twice as long.
Probably not much in that for the average problem.
But, with 100,000 rows and 8 names, my code took 1.4 secs. Yours took quite a bit longer, but also failed to give the correct result, failing without any error message. Try it and see.
Since you raised the topic of error checking, and wishing you no disrespect, wouldn't you regard it as reasonable to check that your own codes perform without error, or at least give some error message when they do go wrong?
So that a user might at least have some reasonable indication, preferably in advance of using it, as to when the code will work and when it won't.Please Login or Register to view this content.
Hi, sorry you carry on.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks