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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Find a value in a sheet and replace it with another value if it matches 8
Move columns of data from one worksheet to another in open WB 3
Excel Copy cells from one worksheet to another workseet - increment row & do again 0
hide colum based on cell value on each worksheets 2
Find Specific Rows in Range and Create New Tab 18
populate combobox from worksheet in different workbook 3
Advanced compare of two worksheets 2
Insert rows at change in cell value 3

Back
Top