Naming Sheets in a Macro

  • Thread starter Thread starter lj
  • Start date Start date
L

lj

I've run accross a problem many times in Excel where I'm running a
macro that creates new sheets - Sheet1, Sheet2, Sheet3 and I rename
these sheets in the macro using the following code:

Sheets("Sheet3").Select
Sheets("Sheet3").Name = Range("A2")

It would be helpful not to have refer to new sheets as
("Sheet1","Shhet2" ect) that i'm in when naming them though as it means
that i have to shut down excel and reopen it every time i want to run
the macro otherwise it will break (since the only time the sheets start
back at 1 is when the document is closed and opened). Any suggestions?
Is there any code i can use to name a sheet that i'm in w/ out
refrencing what it's current name is? Thanks.
 
I've run accross a problem many times in Excel where I'm running a
macro that creates new sheets - Sheet1, Sheet2, Sheet3 and I rename
these sheets in the macro using the following code:

Sheets("Sheet3").Select
Sheets("Sheet3").Name = Range("A2")

It would be helpful not to have refer to new sheets as
("Sheet1","Shhet2" ect) that i'm in when naming them though as it
means that i have to shut down excel and reopen it every time i want
to run the macro otherwise it will break (since the only time the
sheets start back at 1 is when the document is closed and opened).
Any suggestions? Is there any code i can use to name a sheet that
i'm in w/ out refrencing what it's current name is? Thanks.
Click to expand...

Hi,

I may be missing the point, but when you add the new worksheets (I am
assuming via VBA), give them a name there and then:

Set newSht = Worksheets.add

newSht.name = "MyNewSheetName"


HTH,

Alan.
 
or more simply

Worksheets.Add.Name = Range("A2")

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Back
Top