I forgot to name several cells in a sheet that I've copied several times (all in the same workbook).

Is there a way to automate the process of naming the same cell, in each sheet, the same name, just with a local scope?

For example:

In each sheet (say sheets 1-60), I want B3 to be named Date with local scope (sheet only), B5 to be called Intensity with local scope, etc, etc.

4/1/2010 11:22:14 AM

shit nvm

you can do this easily in VBA

hold on

[Edited on April 1, 2010 at 11:31 AM. Reason : asfd]

4/1/2010 11:30:12 AM

yeh, but I just got the VBA book a few weeks ago and haven't gotten far into it. should have learned it a long time ago

4/1/2010 11:32:50 AM

yeahh i'll do it in a min, hold on

Sub Macro2()

k = ActiveSheet.Name
h = ActiveSheet.Index

i = "date" & h
j = "intensity" & h

    ActiveWorkbook.Names.Add Name:=i, RefersToR1C1:="=" & k & "!R1C1"  ' change the second R1C1 to the appropriate cell
    ActiveWorkbook.Names.Add Name:=j, RefersToR1C1:="=" & k & "!R2C1"  ' 

    
End Sub

4/1/2010 11:48:42 AM

thanks. not sure how to loop it, though

and it's undoing the naming as I go through each sheet to run it...?

[Edited on April 1, 2010 at 12:53 PM. Reason : .]

4/1/2010 12:40:21 PM

i dunno.. it's working fine for me. i just went through about 10 sheets and they all turned out right and stayed that way

4/1/2010 1:33:45 PM

ah, it's doing it because I removed the &h in the variable statements...

i = "date" & h

results in naming each cell datesheetnumber but I just need date

ah, I think changing ActiveWorkbook to ActiveSheet will do it

[Edited on April 1, 2010 at 4:44 PM. Reason : .]

4/1/2010 4:39:01 PM

that work?

4/1/2010 10:26:40 PM