Workbook with many Sheets - Need to dynamically create a column and insert sheet name into every row of all sheets

[jholly1984]

Hi everyone,

I am having an excel automation issue that I am hoping someone can help with.

Problem: Being able to automatically add a column and fill the column in with its associated sheet name for multiple sheets at the same time.

Context:
The workbook contains a large number of sheets named by category:

Tab 1 = Category 1
Tab 2 = Category 2
Tab 3 = Category 3
Tab 4+ = Etc.

We want to be able to add a column for every sheet at one time that pulls the tab name into every row as an identifier.

Current State:

Sheet Name 1 = Laptops
Columns = Keyword, Rank, URL

Sheet Names 2+ = Category Name 2+
Columns = Keyword, Rank, URL

Expected Output:

Sheet Name 1 = Laptops
Columns = Keyword, Rank, URL, Category/Sheet Name

We are hoping to be able to insert a macros or formula that will plug the sheet name in across 100+ sheets vs having to do it manually.

Thank you for your time.

 

[jholly1984]

I have this function for being able to dynamically plug the sheet name into a cell:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

What we can't figure out is:

a) How to do this for an entire column dynamically
b) How to do this for an entire column across a large number of sheets dynamically

 

[mumps]

I'm not sure if I understood correctly, but try this macro:

Sub AddSheetName()
    Application.ScreenUpdating = False
    Dim LastRow As Long, ws As Worksheet
    For Each ws In Sheets
        With ws
            LastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            .Range("D1") = "Sheet Name"
            .Range("D2:D" & LastRow) = ws.Name
        End With
    Next ws
    Application.ScreenUpdating = True
End Sub