Filter multiple worksheets based on list

[alkaline55]

Hi everyone, I'm new to VBA and looking for some guidance. I'd like to filter Column A on three different tabs based on the first value of a list on a separate Name tab. So, find value from A2 on Name tab and filter Column A for it on the 3 tabs.

This is part of a wider macro I'm building.

The wider goal of my macro is to complete step 1 above, save down a copy of the workbook titled Name.xlsx, and loop to the next name in the list. Help with the broader goal or the first step is greatly appreciated.

Thank you so much

 

[alkaline55]

They are "Data1", "Data2", "Data3" and they will change names but there will always be 3.

 

[mumps]

If you have to add additional sheets to "Summary" and "Detail", you would have to add them in the code. I'm trying to make that modification as easy as possible for you so I have one more question. Since the names of the 3 "Data" sheets can change, will their position ever change? In other words, will they always be the second, third and fourth sheets when looking at the sheet names at the bottom of you workbook? If not second, third and fourth, will they always be consecutive? If so, what is their position?

 

[alkaline55]

Great! I can keep the Data tabs in the same position, they will always be consecutive and I'll make them the last 3 sheets of the workbook.

For adding additional sheets to "Summary" and "Detail", if I see how you did it in the code, I think I could figure it out but much appreciate making the modification as easy as possible

 

[mumps]

Try this macro. You can add any additional sheets as needed to the array (in red).

Sub FilterSheets()
    Application.ScreenUpdating = False
    Dim LastRow As Long, ws As Worksheet, srcWB As Workbook, desWB As Workbook, srcWS As Worksheet, rName As Range, x As Long
    Set srcWB = ThisWorkbook
    Set srcWS = srcWB.Sheets("Name")
    LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For Each rName In srcWS.Range("A2:A" & LastRow)
        Set desWB = Application.Workbooks.Add(1)
        With srcWB
            For x = .Sheets.Count - 2 To .Sheets.Count
                With .Sheets(x).Cells(1).CurrentRegion
                    .AutoFilter 1, "<>" & rName
                End With
                With .Sheets(x)
                    .Copy after:=desWB.Sheets(desWB.Sheets.Count)
                    desWB.Sheets(desWB.Sheets.Count).AutoFilter.Range.Offset(1).EntireRow.Delete
                    desWB.Sheets(desWB.Sheets.Count).Range("A1").AutoFilter
                    .Range("A1").AutoFilter
                End With
            Next x
            For Each ws In srcWB.Sheets(Array("Summary", "Detail"))
                ws.Copy after:=desWB.Sheets(1)
            Next ws
            Application.DisplayAlerts = False
            Sheets("Sheet1").Delete
            With desWB
                .SaveAs srcWB.Path & Application.PathSeparator & rName & ".xlsx"
                .Close False
            End With
            Application.DisplayAlerts = True
        End With
    Next rName
    Application.ScreenUpdating = True
End Sub

 

[alkaline55]

This works perfectly ! I do have one final final question, however, something I didn't anticipate. Because "Detail" and "Summary" tabs reference my data tabs it looks like those references stick to the original workbook.

Instead of Jordan, Tracey.xlsx referencing Master Workbook data tabs, I would need Jordan,Tracey.xlsx to reference Jordan,Tracey.xslx data tabs.

I think this could be solved by killing references before saving each Name file, but unsure. Is this doable?

 

[alkaline55]

A little more context, if helpful...Data1, Data2, Data3 have data info sorted by Name. So when filtered for just a single Name, the Summary and Detail references will look at row2 (because of headers) and update what is shown.

So if Jordan,Tracey is Name #2 on my Name list, we filter for Jordan,Tracey, clear contents, the references update on Summary and Detail, and we save down a copy Jordan,Tracey.xlsx.

 

[mumps]

I believe it is doable but I would need a copy of your workbook to test it. Could you upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[mumps]

Have you seen my Post #17?

 

[alkaline55]

Yes almost done uploading!

 

[alkaline55]

Ok great, here is the workbook. I made this workbook super simple sample.xlsx