A button to create Sheet2, copy there data from Sheet1 and rename the sheet?

[mintymike]

Hello everyone. I'm trying to get a button in excel 2007 that would basically create a duplicate of Sheet1 into Sheet2.

I've looked around the forum and found this code:

Please Login or Register  to view this content.

But what I'd additionally like to achieve is naming the Sheet2 as ie. Warehouse 2 (and Sheet1 is called, say Warehouse 1) in the process. Is that possible?

[Sean Thomas]

just rename the sheets

Please Login or Register  to view this content.

[mintymike]

Yeah, but I'd like to have a button saying "Create new Warehouse". The button copies Warehouse 1 and automatically names the new sheet Warehouse 2. For instance, I just want to be able to click the button on Warehouse 1 three times and have 3 new sheets, all named automatically Warehouse 2, Warehouse 3, Warehouse 4. So just keep the core "Warehouse" and then add a number as the new sheets go. I'm not sure if I've made it clear enough...

[JOHN H. DAVIS]

One way too do this and probably not the best way is to create a helper sheet for instance "names" down Column A list sequential numbers. Then modify your code as follows:

Please Login or Register  to view this content.

[mintymike]

Hey, that's not bad, actually. Warehouses is just an example here, I guess people dealing with the form I'm trying to build, who'll want to add more sheets, won't go above 50, so that might just do the trick! Thank you!

[JOHN H. DAVIS]

Hey, that's not bad, actually. Warehouses is just an example here, I guess people dealing with the form I'm trying to build, who'll want to add more sheets, won't go above 50, so that might just do the trick! Thank you!

Like I said it's not the best way. However, you're welcome, and I'm glad to help out. Thanks for the feedback. Please comply with Forum Rule 9.

9. Acknowledge the responses you receive, good or bad. If your problem is solved, please say so clearly, and mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved". Or click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Dropdown option or Edit button will not appear -- ask a moderator to mark it.

[mintymike]

Ok, I guess this solves my query. Even if it's not the best way, I can simply specify 100 in the helper Names sheet and that'll work just fine. So I'm saying clearly: the problem is solved, thank you John H. Davis. Marking the thread Solved.