I am working on a problem at work and was hoping that someone here might be able to point me in the right direction.

Here is my task:

I have two worksheets in MS Excel 2003 that I am working with. The goal is to create a function that will allow me to select a value from an AutoFilter menu, reference the column header that is related to that header and find the same header and filter the corresponding column on the second data sheet.

Here is what I have so far:

Sub Macro1()
    Cells.Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=2, Criteria1:="West"
    Columns("A:A").Select
    Selection.Copy = strCellSelect
End Sub

'   Find Header Name
        strHeaderName = WorksheetFunction.Find("strColName", Rows("1:1"), 1)

Sub ActivateWindow2()
       
        ActiveWindow.WindowState = xlNormal
        With ActiveWindow
            .Top = 61
            .Left = -12.5
        End With
        Windows.Arrange ArrangeStyle:=xlHorizontal
        Windows("Copy of Sample Data for MBB (20080508).xls:1").Activate
        Sheets("dataNewDocs").Select

Thanks in advance to everyone.