How to add new sheet with certain name?

[hideki]

Hi,

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

I'm afraid I couldn't write in a correct language but let's put it lik
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 a
Apple2005_08, bananas in Banana2005_08 and so on, where 2005 is curren
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 200

 

[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

 

[Guest]

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())

 

[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

 

[hideki]

Hi all,

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

Thanks for your kindness.
hidek