Apply Formula To Specific Column for Multiple Sheets in a Filtered Table

[Manerlao]

Hi all,

I have an issue with renaming cells in a filtered table and would be grateful for some help from the Excel community!

Objective:

1. For specific sheets in the workbook, apply a filter in column B:B for a specific number of names Company (Parent)
2. Apply a formula to change the Parent name to a Child name which looksup in Column D:D for a specific text, if text is found, then parent name changes to Child name (I have a formula for this)
3. Unfilter table
4. Loop through all sheets


Structure:
I have around 50 sheets in my workbook, with 45 of them having the same structure of headings for range A1 onwards like follows:
So the VBA should only work if cell B1 = Company and D1 = CategoryName

Year	Company	Type	CategoryName
2020-01-01	ParentA	Normal	Child1
2020-01-02	ParentB	Normal	Child2
2020-01-03	ParentC	Normal	Child3
2020-01-04	ParentA	Normal	Child4
2020-01-05	ParentB	Normal	Child5
2020-01-06	ParentC	Normal	Child6

Manually this works easy:

My process is:
Filter column B:B for the a number of parent companies
Copy my formula into RELATIVE CELL B2 and drag down to copy across entire column for B:B.
Then un-filter table.
Go to the next sheet and do the same.


The Formula I currently have is:

=IF(ISNUMBER(SEARCH("Child1",D6)),"Child1",IF(ISNUMBER(SEARCH("Child2",D6)),"Child2",IF(ISNUMBER(SEARCH("Child3",D6)),"Child3","")))

It looks up the cell two spaces to the right and renames the current cell if a criteria is contained in the adjacent cell.
For example:

After the table is filtered, if the cell in D2 adjacent to RELATIVE CELL B2 contains text, Child1 or Child2 or (...), then rename RELATIVE CELL B2 to Child1 OR Child2 or (...)
After the table is filtered, if the cell in D3 adjacent to RELATIVE CELL B3 contains text, Child1 or Child2 or (...), then rename RELATIVE CELL B2 to Child1 OR Child2 or (...)


The code which I recorded works well for a single sheet. My formula above is in a sheet("Scratch") in cell B6.

[/B]
Sub RenameFilteredColBB()

    ActiveSheet.Range("B:B").AutoFilter Field:=2, Criteria1:=Array("Parent1", "Parent2", "Parent3"), Operator:=xlFilterValues
    Sheets("Scratch").Select
    Range("B6").Select
    Selection.Copy
    
    Sheets("Sheet1").Select
    Range("B8").Select
    ActiveSheet.Paste
    Selection.FillDown
    
    ActiveSheet.Range("B:B").AutoFilter Field:=2

End Sub

[B]

I hope this all makes sense! Please let me know if you need any further information on this.

Thank you and I hope someone can kindly help!

Best regards,
Manerlao