Insert Sheet Name for each cell in range

[spencerCCole]

Hello,

I need help creating some code that will automatically fill in each row with the sheet name. I receive a workbook weekly, with about 50 different sheets of data I need to analyze. Doing this manually is very time consuming.

Ex:
Sheet1 is Named KEL01, i need KEL01 filled into every cell in Column A, down to the last row with data in it.
Sheet2 is Named KEL02, i need KEL01 filled into every cell in Column A, down to the last row with data in it.

and so on.

Any suggestions would help!

 

[Yongle]

Should VBA loop though EVERY sheet in the workbook adding the sheet name in column A
Does column A require to be inserted (or is it already there) ?
Which column contains data in every row ?
Will the VBA reside in a different workbook ?

The basic code (untested) is below. Modify to insert column A, set the correct workbook etc

Dim Ws as WorkSheet, Wb As WorkBook, r As Long
Set Wb = ?..?
For Each Ws In Wb.Sheets
  r = Ws.Cells(Rows.Count,"B").End(xlUp).Row
  Ws.Range("A2:A" & r).Value = Ws.Name
  Ws.Range("A1") = "Sheet"
Next Ws