Macro to create new worksheet

[mmcglo1]

I have a workbook and on the first sheet is a button that I would like to attach to a macro. I would like the macro to create a new worksheet and copy data from another sheet inside the same book. This "template" sheet is hidden so nobody messes with it. I just need the contents of that sheet to copy to that new sheet that gets created from clicking the button (macro). I know I have worked workbooks that have done this and much more. I would love to know how to create a form that would populate the new sheet rather than copying data from an exisiting sheet - but this will work and should be easier and quicker to implement.

Thanks

Matt

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[mmcglo1]

I'm sorry but I am not a VB guy. There are portions of the macro that I'm supposed to change with my information from my spreadsheet - could you please point out which? I copy and pasted what you have into my "Macro1" and ran it from MSVBA and it failed.

[JOHN H. DAVIS]

Can you attach a copy of your worksheet for further review. To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button. The macro does not change any information on your spreadsheet. It only adds a sheet and copies the data in your Template sheet to the new sheet, which is what I thought you wanted to accomplish.

[mmcglo1]

I have attached the spreadsheet. Sorry for the delay.

Matt

[JOHN H. DAVIS]

Which Sheet is your template sheet? And what is your naming convention?

[mmcglo1]

the template is Sheet 4

[JOHN H. DAVIS]

I may be wrong, but I'm guessing Sheet4.

Maybe:

Please Login or Register  to view this content.

[mmcglo1]

Error message. Please see the attached screenshot. thanks

[JOHN H. DAVIS]

Your code needs to be the same as the post. You are missing a return after the x and after the word True.

[mmcglo1]

You are a genius!!! Thank you!!!

[mmcglo1]

Can I ask one more questions since you got the workbook? On the Totals sheet you see i have a column of "Ticket Numbers" I defined Names in other sheets where the Ticket Numbers were entered. Do but each time I run my new macro it creates a new sheet and my Ticket numbers and Total Hours columns are no longer correct. Any ideas?

[JOHN H. DAVIS]

I don't know why? When I test it does not affect the Totals sheet at all. The Totals sheet has formulas which reference, the other sheets, not the new sheet. Do you have another macro you are running afterwards, maybe?

[mmcglo1]

This is the only macro. I do have a formulas as you saw. Just simple ones with SUM. I was using the Define Name and then the simple =DefinedName. Is there a way for the macro that creates the new sheet to open a dialog box and ask for the name - then enter that name as the sheet name, enter the name into the next available cell within a range of cells?

[JOHN H. DAVIS]

Are you trying to sum c19 from the new sheet on the Totals Sheet? What does the formula CSN_8 mean? The current macro already asks for a name to be given the new sheet. Still a bit confused.

[mmcglo1]

I am trying to sum E19 to the Totals sheet. But then next to the name of the correct sheet. =CSN_8 is just a link to a Defined Name. If you start typing =C in the formulas bar it will show all of the Names I have Defined. Yes your right it does ask for the name. I should be more specific I guess.

In addition to what the macro does now - have the macro place the 'Enter Name' into the next available cell within a range of cells as show on the Totals sheet?

Thanks

Matt

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[mmcglo1]

So close! It worked with the workbook you are using but I cleared out all of the sheets except Totals, Template and the hidden sheet. When I enter a name for the defined name it gives me an error that refers to this line in the macro - ActiveWorkbook.Names.Add Name:=y, RefersToR1C1:="=R10C1".

I'm quite sure you see what I'm trying to do - create a template for mulitple users to use - to track time by sheet for each month. And then have a simple "Totals" page.

Atttached is my new TEMPLATE file that users will make a copy of each month. Can you take a look when you haev a minute?

[mmcglo1]

Thanks for your time on this by the way. It is very nice of you. I just hope I can pay something forward.

[JOHN H. DAVIS]

It works for me on your new attachment. I didn't receive an error. Remember there are two pop-ups. 1) asks for the new sheet name, 2) asks for the named range name.

[mmcglo1]

Ok - it has to do with the Defined Name. Try and do it again but use the following for both sheet name and range name. INC123456

[mmcglo1]

Ok - it has to do with the Defined Name. Try and do it again but use the following for both sheet name and range name. INC123456