beginner....macro to add new sheet to workbook

[danrau]

hello all i am a beginner self taught and trying to learn vba, macro, and excel functions

i have a problem.

i would like to add a sheet to my workbook. once i add the worksheet to the book i want to name it and then have the name of the worksheet that i just created auto populate on a "master" sheet with all the names that i have added to the workbook. i already have the macro to add the new sheet and prompt to name it and works fine. i just cant figure out how to have it automatically add the name to the "master" sheet.

i.e. i have a workbook that goes by months; jan, feb, mar. i have jan, feb, mar on my mater sheet already. i want to add apr later on and have apr appear on the master sheet after i create the apr sheet. i know it would be easy to just have all the months in already but this is just an example.

any help or tips would be greatly appreciated.

thank you for your time.

[davesexcel]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[danrau]

this is the code that i use to add the new sheets. it copies a hidden "month" sheet to the end of the workbook.

i would like to add or modify the code.

again this is just an example of what i am trying to do.

please let me know if there is any other information that would be needed to assist in giving me ideas to write this.


Sub NewPage()

Sheets("month").Visible = True
Sheets("month").Copy After:=Worksheets(Worksheets.Count)
newpagename = InputBox("Name of month")
ActiveWindow.ActiveSheet.Name = newpagename
Sheets("month").Visible = False
End Sub

[danrau]

this is just a sample book that i created.

thank you davesexcel for your quick response. i look forward to your reply.

[HaHoBe]

Hi, danrau,

please have a look at Forum Rule#3 to understand why you are asked to apply code-tags to your procedure.

Try

Please Login or Register  to view this content.

You would still need to make sure that only legal characters and up to 31 characters would be used. This code enters the name of the sheet directly to the Range A2 of teh new sheet.

Ciao,
Holger

[davesexcel]

Try this.

Please Login or Register  to view this content.

[danrau]

Thank you HaHoBe and davesexcel.

this works like a champ.

looking at the code i dont know how you did it but i will be dissecting it thoroughly to see what and how this works.

Great job guys. this forum rocks.

be well