reference tab value on worksheet

  • Thread starter Thread starter jnewl
  • Start date Start date
J

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
 
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
 
using office 2007. getting an error message on the .range statement.
getting run time error 1004 - method 'range' of 'object' _ worksheet' failed
 
tried mycol = 1 and mycol = 5. same error each time.
none of the sheets were protected
want to insert one column in each worksheet.
 
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.
Click to expand...
 
Back
Top