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

jholly1984

New Member
Joined
Sep 29, 2020
Messages
11
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
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.
 
Last edited by a moderator:

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

jholly1984

New Member
Joined
Sep 29, 2020
Messages
11
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Apr 11, 2012
Messages
9,743
I'm not sure if I understood correctly, but try this macro:
VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,193
Messages
5,546,481
Members
410,742
Latest member
WalterSil
Top