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

Manerlao

Board Regular
Joined
Apr 14, 2020
Messages
50
Office Version
  1. 2019
Platform
  1. Windows
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

YearCompanyTypeCategoryName
2020-01-01ParentANormalChild1
2020-01-02ParentBNormalChild2
2020-01-03ParentCNormalChild3
2020-01-04ParentANormalChild4
2020-01-05ParentBNormalChild5
2020-01-06ParentCNormalChild6


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.

VBA Code:
[/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
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Watch MrExcel Video

Forum statistics

Threads
1,114,021
Messages
5,545,539
Members
410,690
Latest member
navneetr
Top