assign a name to a cell using vb

D

Dave Peterson

Dim myCell as range
set mycell = activesheet.range("x9")
mycell.name = "avalidnamehere"
 
G

Gary''s Student

Sub Macro1()
ActiveWorkbook.Names.Add Name:="qwerty", RefersToR1C1:="=Sheet1!R3C2"
End Sub
 
S

Stefi

ActiveWorkbook.Names.Add Name:="nametoassign", RefersTo:="=Sheet1!A1"
assigns name "nametoassign" to cell A1 in Sheet1.

Regards,
Stefi

„jnewl†ezt írta:
 
J

jnewl

i have multi tabs on a series of worksheets. i am inserting a column at the
beginning of each worksheet and then populating that column with the name on
the tab. i want to name the column in "a1", fee sched id.

i tried what you suggested but i get an error. 'name not found error"

what am i doing wrong


Sub fixdata()
Dim wks As Worksheet
Dim mycol As Long
Dim lastrow As Long
Dim mycell As Range
mycol = 1
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
Set mycell = ActiveSheet.Range("A1")
mycell.Name = "fee sched id"
End With
Next wks
End Sub

thanks
 
D

Dave Peterson

Since you want to use the same name for each worksheet, you'll want to use a
local (or worksheet) level name.

Option Explicit

Sub fixdata()
Dim wks As Worksheet
Dim mycol As Long
Dim lastrow As Long
Dim mycell As Range
mycol = 1
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
.Range("A1").Name = "'" & .Name & "'!fee_sched_id"
End With
Next wks
End Sub

I changed your space characters to underscore in the name of the cell.
 
Top