Placing Workshet Name in cells
 

Hi,

I have 50 worksheets in a workbook,

For each, i need to:

1) Insert a Column at far left
2) For each cell in this Col I need to place the value of th
Worksheet Name

So if there are 127 used rows, and the Worksheet Name is 100100
A1:A127 need to all be populated with the value 100100

My code below doesn't quite work, any tweaking is much appreciated.

Many Thanks


Sub ins()
For Each wks In Worksheets

On Error Resume Next
Dim i
Dim LastRow As Long

Columns("A:A").Select
Selection.Insert Shift:=xlToRight
LastRow = Cells(Rows.Count, 2).End(xlUp).Row

For i = 1 To LastRow
Cells(i, 1) = Worksheet.Name
Next

End Su

--
T De Villier
-----------------------------------------------------------------------
T De Villiers's Profile: http://www.excelforum.com/member.php...fo&userid=2647
View this thread: http://www.excelforum.com/showthread.php?threadid=46945

Placing Workshet Name in cells
 

Hi T,

Try:

'==================
Sub aTester()
Dim wks As Worksheet
Dim LastRow As Long

On Error Resume Next

For Each wks In ThisWorkbook.Worksheets
With wks
LastRow = wks.Cells(Rows.Count, 1).End(xlUp).Row
.Columns("A:A").Insert
.Range("A1:A" & LastRow).Value = wks.Name
End With
Next wks
On Error GoTo 0

End Sub
'<<==================


---
Regards,
Norman



"T De Villiers"
wrote in message
news:T.De.Villiers.1vpeea_1127297129.3195@excelfor um-nospam.com...

Hi,

I have 50 worksheets in a workbook,

For each, i need to:

1) Insert a Column at far left
2) For each cell in this Col I need to place the value of the
Worksheet Name

So if there are 127 used rows, and the Worksheet Name is 100100
A1:A127 need to all be populated with the value 100100

My code below doesn't quite work, any tweaking is much appreciated.

Many Thanks


Sub ins()
For Each wks In Worksheets

On Error Resume Next
Dim i
Dim LastRow As Long

Columns("A:A").Select
Selection.Insert Shift:=xlToRight
LastRow = Cells(Rows.Count, 2).End(xlUp).Row

For i = 1 To LastRow
Cells(i, 1) = Worksheet.Name
Next

End Sub


--
T De Villiers
------------------------------------------------------------------------
T De Villiers's Profile:
http://www.excelforum.com/member.php...o&userid=26479
View this thread: http://www.excelforum.com/showthread...hreadid=469457