Auto sheets .... with common data? [Closed]

Report
Posts
2
Registration date
Monday February 10, 2014
Status
Member
Last seen
February 11, 2014
-
Posts
2670
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 22, 2020
-
Hello,

I would like to have a new sheet generated on open of workbook with sheet name generated in
sequence (i.e. main sheet has common data then each new sheet is named WMR#1... WMR#2.... etc.) ... but
populated with common data to all sheets i.e. Ship to data!
Or maybe not on open but on button click to generate new sheet!

Is there something of a template out there that I could then customize??

Thanks in advance for any and all help!

3 replies

Posts
2670
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 22, 2020
446
Hi Diribarren,

You can create your own template sheet. You can then hide your sheet and use macro to copy the hidden sheet and then rename it.

Macro could look like:
Sub RunMe()
Sheets("Template").Visible = True
Sheets("Template").Copy after:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = "WMR#" & Sheets.Count - 2
Sheets("Template").Visible = False
End Sub

Best regards,
Trowa
Posts
2
Registration date
Monday February 10, 2014
Status
Member
Last seen
February 11, 2014

WOW... this is GREAT! I got is working before but yours is
so much more simpler! Now I just need to figure out how to lock some
of the cells so on the new sheets they can not change them...i.e the
cell that holds the WMR Number!

THANK YOU.... THANK YOU.... THANK YOU!!
Posts
2670
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 22, 2020
446
Hi Diribarren,

Thanks for your positive feedback!

At default all cells are locked. Hit Ctrl+a to select all cells, hit Ctrl+1 to open cell properties, goto final tab (protection), uncheck the Locked box and hit OK.

Now select all the cells you want to lock and do so using cell properties.

Now you have to secure your sheet to activate the locks.

Do all this on your template sheet so you only have to do it once.

Good luck and best regards,
Trowa