How to add new sheet with certain name?

[hideki]

Hi,

I'd like to run a macro that select datas that meet certain criteria and place them in new sheets. How can I create new sheets and give name according to the criteria selected.

I'm afraid I couldn't write in a correct language but let's put it like this example. I've a sheet that contains a long list of fruits (apple, bananas, kiwis etc). Then I'd like to run a macro that put apples in an Apple2005_08, bananas in Banana2005_08 and so on, where 2005 is current year and 08 is current month. How can I name sheets with this pattern?

Your help are much appreciated.

hideki

ps: I'm using Excel 2000

[Zack Barresse]

Hi,

Without seeing any of your code, here is a simple way to name the sheets
with the format you describe ...

With Sheets(1)
.Name = .Range("A1").Value & Year(Date) & "_" & Format(Month(Date),
"mm")
End With

Change the first "Sheets(1)" to whatever sheet you want to rename.

HTH

--
Regards,
Zack Barresse, aka firefytr


"hideki" <hideki.1tm0ij_1123780144.3682@excelforum-nospam.com> wrote in
message news:hideki.1tm0ij_1123780144.3682@excelforum-nospam.com...
>
> Hi,
>
> I'd like to run a macro that select datas that meet certain criteria
> and place them in new sheets. How can I create new sheets and give name
> according to the criteria selected.
>
> I'm afraid I couldn't write in a correct language but let's put it like
> this example. I've a sheet that contains a long list of fruits (apple,
> bananas, kiwis etc). Then I'd like to run a macro that put apples in an
> Apple2005_08, bananas in Banana2005_08 and so on, where 2005 is current
> year and 08 is current month. How can I name sheets with this pattern?
>
> Your help are much appreciated.
>
> hideki
>
> ps: I'm using Excel 2000
>
>
> --
> hideki
> ------------------------------------------------------------------------
> hideki's Profile:
> http://www.excelforum.com/member.php...o&userid=18903
> View this thread: http://www.excelforum.com/showthread...hreadid=395035
>

[Jim Thomlinson]

Here is how you add a sheet and give it a name (based on the value in Cell A1
of the activesheet and the current year and month)

dim wksActive as worksheet
dim wksNew as worksheet

set wksActive = activesheet
set wksnew = worksheets.add

wksnew.name = wksactive.range("A1").value & year(date()) & "-" & month(date())

--
HTH...

Jim Thomlinson


"hideki" wrote:

>
> Hi,
>
> I'd like to run a macro that select datas that meet certain criteria
> and place them in new sheets. How can I create new sheets and give name
> according to the criteria selected.
>
> I'm afraid I couldn't write in a correct language but let's put it like
> this example. I've a sheet that contains a long list of fruits (apple,
> bananas, kiwis etc). Then I'd like to run a macro that put apples in an
> Apple2005_08, bananas in Banana2005_08 and so on, where 2005 is current
> year and 08 is current month. How can I name sheets with this pattern?
>
> Your help are much appreciated.
>
> hideki
>
> ps: I'm using Excel 2000
>
>
> --
> hideki
> ------------------------------------------------------------------------
> hideki's Profile: http://www.excelforum.com/member.php...o&userid=18903
> View this thread: http://www.excelforum.com/showthread...hreadid=395035
>
>

[Ron de Bruin]

Hi

You can use a macro to do this
Change the naming of the sheets in the example code

http://www.rondebruin.nl/copy5.htm



--
Regards Ron de Bruin
http://www.rondebruin.nl


"hideki" <hideki.1tm0ij_1123780144.3682@excelforum-nospam.com> wrote in message
news:hideki.1tm0ij_1123780144.3682@excelforum-nospam.com...
>
> Hi,
>
> I'd like to run a macro that select datas that meet certain criteria
> and place them in new sheets. How can I create new sheets and give name
> according to the criteria selected.
>
> I'm afraid I couldn't write in a correct language but let's put it like
> this example. I've a sheet that contains a long list of fruits (apple,
> bananas, kiwis etc). Then I'd like to run a macro that put apples in an
> Apple2005_08, bananas in Banana2005_08 and so on, where 2005 is current
> year and 08 is current month. How can I name sheets with this pattern?
>
> Your help are much appreciated.
>
> hideki
>
> ps: I'm using Excel 2000
>
>
> --
> hideki
> ------------------------------------------------------------------------
> hideki's Profile: http://www.excelforum.com/member.php...o&userid=18903
> View this thread: http://www.excelforum.com/showthread...hreadid=395035
>

[hideki]

Hi all,

Thank you for all the replies. It works perfectly as I desired. I really appreciated all of them.

Thanks for your kindness.
hideki