Auto create and name sheet

I am writing a macro where when the user clicks a button, it will add a new sheet and automatically draw up a template. I want it where the new sheet is created it's named "Panel (1)" simple enough, however, if they click the add panel button again, I want the next sheet to be named "Panel (2)" and so forth.

If I use this at the beginning of my code, it will create the new sheet named "Panel (1)"

Please Login or Register  to view this content.

But when I try again to add a new sheet, I get an error that the sheet name already exists, and I can't figure out how to make the numerical value add +1 to the last pre-existing sheet created.

Thoughts?

Hi there,

See if the following code does what you need:

Please Login or Register  to view this content.

The highlighted values can be altered to suit your own requirements.


Hope this helps - please let me know how you get on.

Regards,

Greg M

Originally Posted by mehmetcik

Please Login or Register  to view this content.

Thank you, this seems to work great!

Hi again,

Many thanks for the Reputation increase - much appreciated!

Best regards,

Greg M

I could use a little more help if you don't mind. I am also trying to add a button that will copy an active sheet and auto name it the same way as above.

Suppose active sheet is named "Qty (1)", when the user clicks the button, it will copy the sheet and the new sheet would be named "Qty (2)" and so forth. No matter what sheet they copy, I want the new sheet to always be named Qty (+1 to last sheet that was created) every time.

I tried using the same coding you provided me for the panel sheets and changed to the following:

Please Login or Register  to view this content.

It will work once and create a "QTY (1)" sheet, but when I try it again, I get a "run-time error '1004: That name is already taken. Try a different one."

Do I need to change any other values in the code above to achieve this?

As long as the right most sheet is the last "Qty" sheet, this should work.

Please Login or Register  to view this content.

In your last Post you mention copying a sheet but your code says to add a new sheet.
I took it to be adding a new sheet.

Originally Posted by jolivanes

As long as the right most sheet is the last "Qty" sheet, this should work.

Please Login or Register  to view this content.

In your last Post you mention copying a sheet but your code says to add a new sheet.
I took it to be adding a new sheet.

Thanks for your help! I need it to copy active sheet and name it accordingly. Also, the sheet will not always be on the right most sheet unfortunately.

Please don't quote.
Just use the person's handle if you answer a particular poster.

Please Login or Register  to view this content.