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

#### Manerlao

##### Board Regular
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.

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

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Replies
2
Views
349
Replies
0
Views
293
Replies
17
Views
1K
Replies
0
Views
193
Replies
12
Views
412