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

 

[mumps]

The "Detail" sheet has 3 people listed in column A. Do you want that sheet to be copied will all 3 names to all the new files or do you want that sheet to contain only the name that corresponds to the name in the file name?

 

[alkaline55]

True that the 3 people are listed, but because the filter on the Data tabs will be just that filtered Name, the references will update. So nothing needs to be altered on the non Data tabs except a step to kill the references or maybe a copy paste special. Let me know if that description makes sense

Also to note, in my actual workbook I have iferror statements to account for any reference errors if there are less rows than what is referenced. So although I'm referencing up to Data1$A15 it would just be blank if nothing is there.

 

[mumps]

Try:

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("Names")
    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)
                    With desWB.Sheets(desWB.Sheets.Count)
                        .AutoFilter.Range.Offset(1).EntireRow.Delete
                        .Range("A1").AutoFilter
                    End With
                    .Range("A1").AutoFilter
                End With
            Next x
            For Each ws In srcWB.Sheets(Array("Summary", "Detail"))
                ws.copy after:=desWB.Sheets(1)
                ActiveSheet.UsedRange.Replace What:="[" & srcWB.Name & "]", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            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 is excellent! It works perfectly. Thank you so much for your help and patience

 

[mumps]

My pleasure.

 

[alkaline55]

For my understanding, what does the chunk and red line do below?

Next ws
Application.DisplayAlerts = False
Sheets("Sheet1").Delete
With desWB

In my actual workbook I seem to get a compile error at that line when I try to run, but have accounted for all the sheets. When I comment it out, it seems to start to run properly but it then keeps prompting me for Update Values location for each sheet (Summary, Detail) like it's referencing a outside of the workbook. Is there something I need to account for here when moving to my more complicated workbook..

 

[alkaline55]

I have also confirmed that all formula references are within the 1 workbook, no references to any external sources. Although the macro seems to think so..

 

[mumps]

This line of code:

Set desWB = Application.Workbooks.Add(1)

adds a new workbook with one blank sheet which by default is always named "Sheet1". Each new workbook must have at least one sheet. The red line of code deletes that sheet since it isn't needed. Each time you delete a sheet, Excel pops up a message asking you to confirm the deletion. The line above the red line disables that pop-up message.

 

[mumps]

Although the macro seems to think so..

Please clarify in detail.

 

[alkaline55]

Here are a couple screen shots. When I commented it out, I get the second screen shot, a pop up prompting me to Update Values (I have black marked some things for privacy reasons but you can see what I mean). But this is probably because of an issue with the initial error.