| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| I want in one worksheet to relatively link to/reference cells in another without changing the format of the current worksheet. | Excel Discussion (Misc queries) | |||
| Reference to ActiveX control on worksheet requires full worksheet name | Excel Programming | |||
| Reference the worksheet from a multiple worksheet range function ( | Excel Worksheet Functions | |||
| Can the offset worksheet function reference another worksheet | Excel Worksheet Functions | |||
| Altering code to reference the worksheet before the active worksheet | Excel Programming | |||
Linear Mode
