Add name using VBA? (Using variables in RefersTo) 2

[rlee16]

I would like to name a particular cell in every worksheet "Sales" using VBA. Thus Sheet A would have A!Sales and Sheet B would have B!Sales. The following is a snippet of my very amateurish code. How can I code the RefersTo:= so that it is using variables instead of the hard coded cell address? Thanks in advance.

__________________________________________
Selection.Find(What:="Sales", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=True).Cells(1, 10).Select
TotRev = ActiveCell.Value
TotRevRow = ActiveCell.Row
TotRevCol = ActiveCell.Column

NewTicker = TickerSymbol & "!Sales"

ActiveWorkbook.Names.Add Name:=NewTicker, RefersTo:="=a!$J$104"

________________________________________

Richard

 

[SkipVought]

Hi,

dim ws as worksheet
for each ws in worksheets
   ActiveWorkbook.Names.Add _
    Name:="Sales", _
    RefersTo:="='" & ws.name & "'!$J$104"
next

Skip,
[sub]
[red]Be Advised![/red] For those this winter, caught in Arctic-like calamity, be consoled...
Many are cold, but few are frozen![/sub]

 

[PHV]

Something like this ?
RefersTo:="=" & ActiveSheet.Name & "!" & ActiveCell.Address

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

oops

For Each ws In Worksheets
   [b]ws[/b].Names.Add _
    Name:="Sales", _
    RefersTo:="='" & ws.Name & "'!$J$1"
Next

Skip,
[sub]
[red]Be Advised![/red] For those this winter, caught in Arctic-like calamity, be consoled...
Many are cold, but few are frozen![/sub]

 

[rlee16]

Thanks Skip and PHV,

I combined both of your postings into the following workable solution. Those little single quotes were tough to debug!

RefersTo:="='" & ActiveSheet.Name & "'!" & ActiveCell.Address

 

[SkipVought]

The single quotes are only necessary IF there are spaces in the Sheet Name. I use them regardless.

Skip,
[sub]
[red]Be Advised![/red] For those this winter, caught in Arctic-like calamity, be consoled...
Many are cold, but few are frozen![/sub]