reference tab value on worksheet

[jnewl]

have a workbook with several sheets. want to insert a column in each
worksheet and populate that column with the value indicated on the tab. how
do i code in vb? thanks

 

[Dave Peterson]

Maybe something like:

Option Explicit
Sub testme()
Dim wks As Worksheet
Dim myCol As Long
Dim LastRow As Long

myCol = 5
For Each wks In ActiveWorkbook.Worksheets
With wks
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Columns(myCol).Insert
.Range(.Cells(1, myCol), .Cells(LastRow, myCol)).Value = "'" & wks.Name
End With
Next wks

End Sub

 

[jnewl]

using office 2007. getting an error message on the .range statement.
getting run time error 1004 - method 'range' of 'object' _ worksheet' failed

 

[Dave Peterson]

What was myCol equal to when you ran it?

Were any of your sheets protected?

 

[jnewl]

tried mycol = 1 and mycol = 5. same error each time.
none of the sheets were protected
want to insert one column in each worksheet.

 

[Dave Peterson]

Did you change any thing else in the code?

It worked fine for me.

tried mycol = 1 and mycol = 5. same error each time.
none of the sheets were protected
want to insert one column in each worksheet.

 

[jnewl]

works just fine in 2000. had the error in 2007.

thanks for your help

 

[Dave Peterson]

Did you change any thing else in the code?

works just fine in 2000. had the error in 2007.

thanks for your help