Auto sheets .... with common data?Closed

Question

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!

TrowaD · Answer

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 = TrueSheets("Template").Copy after:=Sheets(Sheets.Count)Sheets(Sheets.Count).Name = "WMR#" & Sheets.Count - 2Sheets("Template").Visible = FalseEnd Sub

Best regards,
Trowa

diribarren · Answer

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!!

TrowaD · Answer

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

Auto sheets .... with common data?

3 responses

Similar discussions

Subscribe To Our Newsletter!