How can I syncronize Autofilters on multiple sheets?

[MikeZz]

Hi,
I have a macro that creates a workbook with 2 sheets:
Sheet 1: Vehicle Information (500+ records)
10 Columns in a record that define a unique vehicle.
Sheet 2: Components in each Vehicle (28000+ records)
Same 10 Columns in a record that define a unique vehicle.
In db terms, the 10 columns are like the primary key from sheet 1.
15 additional columns which further define each comonent in each
vehicle.
Each record represents a unique Vehicle/Component combination.

I know this would be a great candidate for a real db but I don't have that
option.

I have my macro create the workbook and put auto filters on both sheets.

I'd like to somehow copy code from my master workbook to the new workbook
that will syncronize the auto filters on both sheets when ever the other one
is changed.

The column titles on Sheet 1 are duplicated on Sheet 2 but could be in a
different order (don't ask why... to hard to explain). Hopefully this won't
matter.

Anyway, I was thinking that I could probably do something in the Worksheet
Change Event... although I've never done anything other than straight Module
VBA programming.

So, what's the best way to do this and also, how do I get the code from my
Master file into the newly created file? I found the attached code off of
google but it doesn't explain exactly how I can embed it into a worksheet as
opposed to a module.

Thanks!
MikeZz


Sub CopyModule(SourceWB As Workbook, strModuleName As String, _
TargetWB As Workbook)
' copies a module from one workbook to another
' example:
' CopyModule Workbooks("Book1.xls"), "Module1", _
Workbooks("Book2.xls")
Dim strFolder As String, strTempFile As String
strFolder = SourceWB.Path
If Len(strFolder) = 0 Then strFolder = CurDir
strFolder = strFolder & "\"
strTempFile = strFolder & "~tmpexport.bas"
On Error Resume Next
SourceWB.VBProject.VBComponents(strModuleName).Export strTempFile
TargetWB.VBProject.VBComponents.Import strTempFile
Kill strTempFile
On Error GoTo 0
End Sub

 

[Tom Ogilvy]

you can loop through the filters collection of the Autofilter object and
check the the criteria1, criteria2, operator and other properties. Get a
list of all the criteria and field headers and then apply that to the other
autofilter.

The change event doesn't fire when you change the criteria of an autofilter.
It depends on your functionality, but the time to synchronize might be when
the sheet is selected. You could use the deactivate event for each sheet.
Again, it depends on when they must be synchronized and which one is the
"master" in that context.

 

[MikeZz]

Hi Tom, Thanks for the reply.

Your suggestion to do the update when I "deactivate" one sheet or select the
new sheet may work. I think it would be best to do it when I de-activate the
current sheet.... apply those filters to the other one. That way I assume
the last set of filters is the one you want to use.

I haven't done any VBA with event changes or activate / deactivate etc...
don't even know what the subs would be called.

Can you rough out or point to an example of what the code would look like?
(particularly the syntax and structure).

Also an example showing how to go through each filtered column on a sheet
and get properties such as: column reference (or name/title + the value of
the filter.

With respect to "Master" context... I may need to explain better.

I have a Master workbook which just contains macros and info the Macro needs
to run.
I have a Raw Data workbook which has 28000 raw data records.
The Macro creates a new workbook that has a sheet with the Raw Data + extra
data I calculate for each record, and another sheet called "Unique Vehicles".
"Unique Vehicles" is like a Primary Key list for the raw data. The new
workbook is created by the macro and what I need to do is copy a VBA Module
from the Master Workbook to this newly created workbook. This module would
have all the code to run the "syncronize auto filter" feature. Does this
better explain what I meant?