Macro or VBA Help needed for Advanced Filter and auto update destination workbooks

Hi Folks,

I think this is an extremely tough one, not even sure this is possible!

I need a Macro or VBA to do an advanced filter based on multiple criteria (Filter Criteria = Cell values of "T2 to xldown), then copy advanced filtered data and paste it into new sheets individually (e.g. Add sheets for "SDG", "OPD", OTD" etc.). Please note that the criteria may vary.
Also I have some other workbooks which are I already saved in a share drive ("D:\Public\users\") the workbooks names are same as Filter Criteria (e.g. "SDG.xlsx", "OPD.xlsx", "OTD.xlsx"). After run the advanced filter Macro or VBA then I want to update all these workbooks with advanced filtered data. Is this possible by using a Macro or VBA?.

I have attached a excel file of what I'm trying to accomplish. Thanks in advance for your expertise!

Joshi

Joshi

The code below will create the sheets and do the filtering in the current workbook.

Sub aaa()
  Sheets.Add before:=Sheets(1)
  ActiveSheet.Name = "Working"
  
  Sheets("Master").Activate
  Set datarng = Range("A2:T" & Cells(Rows.Count, 1).End(xlUp).Row)
  Range("T2:T" & Cells(Rows.Count, "T").End(xlUp).Row).AdvancedFilter Action:=xlFilterCopy, copytorange:=Sheets("Working").Range("A1"), unique:=xlYes
  With Sheets("Working")
    .Range("B1").Value = .Range("A1").Value
    For Each ce In .Range("A2", .Range("A2").End(xlDown))
      .Range("B2").Value = ce.Value
      Sheets.Add after:=Sheets(Sheets.Count)
      ActiveSheet.Name = ce.Value
      ActiveSheet.Range("A2:T2").Value = Sheets("Master").Range("A2:T2").Value
      datarng.AdvancedFilter Action:=xlFilterCopy, copytorange:=Range("A2:T2"), criteriarange:=.Range("B1:B2")
    Next ce
  End With
  
  Application.DisplayAlerts = False
  Sheets("Working").Delete
  Application.DisplayAlerts = True
End Sub

rylo

Hi rylo,

The code is not working. Its cleared all the data from my Master Sheet and added some sheets with only the headers of the master sheet.

Please help me to fix the issue.

Thanks,

Joshi

Hi

I opened your example file, went to the existing macro module, and inserted the code from post #2. Selected and ran the code. It created 4 new sheets, filtered the data, and didn't touch the master sheet.

Exactly what are you doing?

rylo