Copy a template sheet to end of workbook using a InputBox to rename

[NZ_Shane]

Hi, my first post and only new to VBA so if I'm in the wrong area or haven't supplied enough info I apologise !!

I want to have a button set up so users can click it - a inputbox opens up "Enter New Sheet Name" then once you type that in it copies the two template sheets I have set up and pastes them to the end of the workbook with the entered name followed by "_Form" and "Chart"


This is what I currently have -

Please Login or Register  to view this content.

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #3 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer

It creates the one sheet I have in the macro but then gets stuck in a loop...?

Any help would be really appreciated!!

Thank you in advance

[6StringJazzer]

First, variable wks is undeclared, and you are ignoring errors. Most likely the line

Please Login or Register  to view this content.

is producing an error which your code is ignoring. I recommend you use Option Explicit to guarantee that all variables are declared.

Also, this is a strange condition to test for:

Please Login or Register  to view this content.

Also, you are copying the sheet first, and giving the user no opportunity to cancel the operation. You should get the name from the user first, exiting if he changes his mind.

I would suggest this rewrite. Note that sName is declared as Variant, because InputBox returns a Variant. It will be a string if the user entered a string, and it will be FALSE if the user pressed Cancel.

In this solution, if the user does not enter a sheet name, no copy is created. If the user enters a sheet name but the result is invalid, the new sheet is deleted and the user gets an error message.

Please Login or Register  to view this content.

[NZ_Shane]

Thank you very much!! That work straight away! Looks like I still have a lot of learning to do! Its all very interesting though.

My next task is I want to add another sheet to be copied into the macro, I want it to copy a different template sheet (Template_Chart) but rename it the same 'sName' from the InputBox, but "_Chart" after instead.... I'll see if I can add this into your solution.

Many thanks again, very kind of you!!

[6StringJazzer]

I have not tested this but I think it's straightforward and I expect it to work.

Please Login or Register  to view this content.

[NZ_Shane]

Thanks! I did a similar thing but both keep failing at

Please Login or Register  to view this content.

I have checked the sheet name is correct and even tried renaming the sheet and in the macro to something different but still fails at the same point.
Error is 'Run-time error "9":
Subscript out of range

Just when I thought I was making sense out of it all.

[6StringJazzer]

That error indicates that there is no worksheet named Chart_Template. I can diagnose it if you attach your file.

Under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.

[NZ_Shane]

Thank you, again!!

Sorry - File was to big - have compressed it now - should work this time.

[6StringJazzer]

Chart_Template is not a Worksheet, it's a Chart.

Please Login or Register  to view this content.

Bedtime in the States now....

[NZ_Shane]

You are an absolute legend!!!

Thank you very much kind Sir!!!!

[6StringJazzer]

You are very welcome and thanks for the rep!

If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."