Automatic unique records filter macro

[gferrer010]

I continuously update a database - it has multiple columns - and have
assigned a dynamic range name to it ("List"). Since the database has
some duplicate records, very often I end up performing the Data/
Filter/Advanced Filter routine and copying the unique records onto
another sheet (same workbook).

Would it be possible to automate this command? Is there a macro that
would copy the unique records to another sheet automatically, while
the workbook is open, without having to push a command button, or
opening/closing the file? Thanks in advance.

Regards,
George Ferrer

 

[Dave Peterson]

I would create my own macro.

I'd start by recording a macro when I did all the work manually.

 

[gferrer010]

I would create my own macro.

I'd start by recording a macro when I did all the work manually.

Dave,

If I record a a macro I still have to use the command/radio buttons in
order to execute the macro though. If possible, I'd rather avoid
that. The reason being that another person is going to take over the
maintenance of this database and he doesn't even know the basics of
Excel.
I'm not even sure if the automation of this task is possible. Is there
a VBA code that would do this?

Thanks,
George

 

[Dave Peterson]

The recorded code won't show the dialogs for you to chose options. It'll just
use the options that you used when you recorded the macro.

Try recording the macro and see if it gets close to what you want. If you have
trouble tweaking it, post back with your macro and what tweaks you need.

 

[gferrer010]

The recorded code won't show the dialogs for you to chose options.  It'll just
use the options that you used when you recorded the macro.

Try recording the macro and see if it gets close to what you want.  If you have
trouble tweaking it, post back with your macro and what tweaks you need.








--

Dave Peterson- Hide quoted text -

- Show quoted text -

Dave, here is the macro that I came up with:

Sub ExtractUniqueRecords()

Keyboard Shortcut: Ctrl+z

Range("List”).AdvancedFilter Action:=xlFilterCopy,
CopyToRange:=Range( _
"A2"), Unique:=True

End Sub

This seems to work OK, but I either have to use the keyboard shortcut
or assign a button in order to run it. It's not capable of running by
itself, without any (manual) commands.

 

[Dave Peterson]

That seems pretty reasonable to me.

Why and when would you want it to run automatically?

 

[gferrer010]

That seems pretty reasonable to me.  

Why and when would you want it to run automatically?







(e-mail address removed) wrote:








--

Dave Peterson- Hide quoted text -

- Show quoted text -

I just want to eliminate any potential mistakes, since the person
who's gonna be doing the data entry is rather mistake prone, and is
most likely going to forget to use the macro command. Automating the
whole thing whould eliminate lots of duplication errors. Ideally the
macro would run by itself as soon as new pieces of data/records are
added to the database.

 

[Dave Peterson]

Personally, I wouldn't do it that way.

If your database has 18 fields, you'd choose one to indicate that you're done
with the data entry (the 18th field???). Then if the user notices a typo, your
automatic filter may have hidden the row and it may be difficult to find. Or if
the user just starts entering data in your indicator field, it could be a
problem for the user.

But if you wanted, you could use an event macro (worksheet_change) that would
look for changes in certain ranges and run your routine.

But I don't know enough to offer any real details.