excel - Dynamically populate column with worksheet name -

i need populate cells within specific column name of worksheet.

i have following code populating single cell:

sub worksheet_name_plop()     cells.wraptext = false ' disables wordwrap     [ag2].value = activesheet.name     columns("ag").select     selection.entirecolumn.autofit end sub 

the trouble i'm having there may anywhere 1 10,000+ lines of data per worksheet. not sure of how populate rows have data.

there header row it's important results start on second row of each worksheet.

for efficiency: need able across worksheets of same file.

any assistance appreciated!

10 million rows in 9 seconds:

option explicit  public sub setid1()     const first_row long = 2     const col       string = "ag"     dim ws worksheet, lastrow long, t double, tr long      application.screenupdating = false: t = timer     each ws in application.activeworkbook.worksheets         lastrow = ws.usedrange.row + ws.usedrange.rows.count - 1          ws.range(col & first_row & ":" & col & lastrow).value2 = ws.name         ws.cells(first_row, col)             .wraptext = false             .entirecolumn.autofit         end         tr = tr + lastrow - first_row + 1     next     debug.print "setid1 - sheets: " & worksheets.count & _                        ", rows: " & tr & ", duration: " & timer - t     application.screenupdating = true end sub 

---

public sub setid2()     const first_row long = 2     const col       string = "ag"     dim ws worksheet, lastrow long, t double, tr long      application.screenupdating = false: t = timer     each ws in application.activeworkbook.worksheets         lastrow = ws.usedrange.row + ws.usedrange.rows.count - 1          ws.cells(first_row, col)             .value2 = ws.name             .wraptext = false             .entirecolumn.autofit         end         ws.range(col & first_row & ":" & col & lastrow).filldown         tr = tr + lastrow - first_row + 1     next     debug.print "setid2 - sheets: " & worksheets.count & _                        ", rows: " & tr & ", duration: " & timer - t     application.screenupdating = true end sub 

---

tests:

setid1 - sheets: 10, rows: 10000000, duration: 9.08203125 setid1 - sheets: 10, rows: 10000000, duration: 9.064453125 setid1 - sheets: 10, rows: 10000000, duration: 9.0625  setid2 - sheets: 10, rows: 10000000, duration: 8.580078125 setid2 - sheets: 10, rows: 10000000, duration: 8.58203125 setid2 - sheets: 10, rows: 10000000, duration: 8.56640625