Macro to Name Sheet

[Dolphinv4]

Hi,

I want my sheet name to automatically be named as Date(Adj) whereby the Date
will be obtained from cell A1. This is what i did but it don't work. What's
wrong?

Sub New_Month()

Dim myAdjName As String

myAdjName = Format(Range("A1").Value, "mmm") & "(ADJ)"
ActivSheet.Name = myAdjName

End Sub

Thanks!

[HiArt]

Hi Dolphinv4,

I have had the same problem with Range, the macro looks like it should work, but doesn't.

The solution is that Range sometimess needs to be in the format expression.range. For your macro try:

myAdjName = Format(Worksheets(1).Range("A1").Value, "mmm") & "(ADJ)"

Adjust the Worksheets(x) value accordingly.

HTH

Art

[Stefi]

You misspelled
> ActivSheet.Name = myAdjName
Insert an "e":
ActiveSheet.Name = myAdjName
Regards,
Stefi


„Dolphinv4” ezt �*rta:

> Hi,
>
> I want my sheet name to automatically be named as Date(Adj) whereby the Date
> will be obtained from cell A1. This is what i did but it don't work. What's
> wrong?
>
> Sub New_Month()
>
> Dim myAdjName As String
>
> myAdjName = Format(Range("A1").Value, "mmm") & "(ADJ)"
> ActivSheet.Name = myAdjName
>
> End Sub
>
> Thanks!

[Dolphinv4]

Hi,

the prob is not with the typo. I realised it later and changed but it still
don't work.

Regards,
Dolphin

"Stefi" wrote:

> You misspelled
> > ActivSheet.Name = myAdjName
> Insert an "e":
> ActiveSheet.Name = myAdjName
> Regards,
> Stefi
>
>
> „Dolphinv4” ezt �*rta:
>
> > Hi,
> >
> > I want my sheet name to automatically be named as Date(Adj) whereby the Date
> > will be obtained from cell A1. This is what i did but it don't work. What's
> > wrong?
> >
> > Sub New_Month()
> >
> > Dim myAdjName As String
> >
> > myAdjName = Format(Range("A1").Value, "mmm") & "(ADJ)"
> > ActivSheet.Name = myAdjName
> >
> > End Sub
> >
> > Thanks!

[Dolphinv4]

Hi,

but I need this macro to work in every new sheet created, thus I can't put
in a specific "worksheet" name. I tried using "ActiveSheet" instead but it
doesn't work. Please help!

myAdjName = Format(ActiveSheet.Range("A1").Value, "mmm") & "(ADJ)"
ActiveSheet.Name = myAdjName

Thanks,
Val.
"HiArt" wrote:

>
> Hi Dolphinv4,
>
> I have had the same problem with Range, the macro looks like it should
> work, but doesn't.
>
> The solution is that Range sometimess needs to be in the format
> -expression-.range. For your macro try:
>
> myAdjName = Format(*Worksheets(1).*Range("A1").Value, "mmm") & "(ADJ)"
>
> Adjust the Worksheets(x) value accordingly.
>
> HTH
>
> Art
>
>
> --
> HiArt
> ------------------------------------------------------------------------
> HiArt's Profile: http://www.excelforum.com/member.php...o&userid=19953
> View this thread: http://www.excelforum.com/showthread...hreadid=472032
>
>

[Dolphinv4]

Hi,

i just realised it does work after I used "ActiveSheet" in your formula.
Thanks!

Val

"Dolphinv4" wrote:

> Hi,
>
> but I need this macro to work in every new sheet created, thus I can't put
> in a specific "worksheet" name. I tried using "ActiveSheet" instead but it
> doesn't work. Please help!
>
> myAdjName = Format(ActiveSheet.Range("A1").Value, "mmm") & "(ADJ)"
> ActiveSheet.Name = myAdjName
>
> Thanks,
> Val.
> "HiArt" wrote:
>
> >
> > Hi Dolphinv4,
> >
> > I have had the same problem with Range, the macro looks like it should
> > work, but doesn't.
> >
> > The solution is that Range sometimess needs to be in the format
> > -expression-.range. For your macro try:
> >
> > myAdjName = Format(*Worksheets(1).*Range("A1").Value, "mmm") & "(ADJ)"
> >
> > Adjust the Worksheets(x) value accordingly.
> >
> > HTH
> >
> > Art
> >
> >
> > --
> > HiArt
> > ------------------------------------------------------------------------
> > HiArt's Profile: http://www.excelforum.com/member.php...o&userid=19953
> > View this thread: http://www.excelforum.com/showthread...hreadid=472032
> >
> >