reference tab value on worksheet
 

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

reference tab value on worksheet
 

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 wrote:

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

reference tab value on worksheet
 

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

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 wrote:

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

reference tab value on worksheet
 

What was myCol equal to when you ran it?

Were any of your sheets protected?



jnewl wrote:

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

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 wrote:

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


--

Dave Peterson

reference tab value on worksheet
 

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" wrote:

What was myCol equal to when you ran it?

Were any of your sheets protected?



jnewl wrote:

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

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 wrote:

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


--

Dave Peterson

reference tab value on worksheet
 

Did you change any thing else in the code?

It worked fine for me.

jnewl wrote:

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" wrote:

What was myCol equal to when you ran it?

Were any of your sheets protected?



jnewl wrote:

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

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 wrote:

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


--

Dave Peterson


--

Dave Peterson

reference tab value on worksheet
 

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

thanks for your help

"Dave Peterson" wrote:

Did you change any thing else in the code?

It worked fine for me.

jnewl wrote:

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" wrote:

What was myCol equal to when you ran it?

Were any of your sheets protected?



jnewl wrote:

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

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 wrote:

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


--

Dave Peterson


--

Dave Peterson

reference tab value on worksheet
 

Did you change any thing else in the code?

jnewl wrote:

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

thanks for your help

"Dave Peterson" wrote:

Did you change any thing else in the code?

It worked fine for me.

jnewl wrote:

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" wrote:

What was myCol equal to when you ran it?

Were any of your sheets protected?



jnewl wrote:

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

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 wrote:

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


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson