Macro to check if Sheet exists and if not, create it using a template

[xavisaus]

Hello

I am trying to make a macro that, when pressing a hyperlink to another sheet on the same book, checks if the hyperlink is valid (ergo the sheet exists), and if it does not exist, copy a defined Template with the name given on a cell. This would have to work for a whole table.

The image shows the table I created, where the column P is column A, starting on 11 and colun P/N is column B, starting on 11. The idea is that if I click on the hyperlink on P/N GENERAL, the macro automatically checks if the sheet is created and if not, create it copying it from a template, and renaming using the value contained on the cell on the left side.

Example, I click on the hyperlink CM000000-000. If it is created, the hyperlink will take me to that sheet. If not, the macro will copy a "Template" and rename it to 100000_0 (value on the left of the hyperlink).
Same with all positions on the column P/N GENERAL

Additionally, if the value on the column P/N GENERAL contains a "-", a different template should be used for the copy. Let's say "Template2"

Thanks!

 

[bebo021999]

So, where is "template"? Is it a worksheet? or a range in specific sheet?

 

[xavisaus]

Hello, Template will be a worksheet placed on the same Book

 

[fadee2]

hi xavisaus,
Welcome to the board.

I would suggest providing a sample file containing your initial template and desensitized master data. This would enable everyone trying to help, to better understand the query and suggest solutions accordingly, rather then interpreting the query and making assumptions.

In the meantime, go through below post and see if you find it helpful.

Monthly workbook with daily worksheets...Help please

Hi, *First timer here* I'm hoping someone can help - i may be overthinking things so apologies in advance. I use a monthly workbook with worksheets for days of the month to capture transport usage data... so 30-31 sheets in each workbook for every month. Is there a way to simplify this...

www.mrexcel.com

hth....

 

[xavisaus]

hi xavisaus,
Welcome to the board.

I would suggest providing a sample file containing your initial template and desensitized master data. This would enable everyone trying to help, to better understand the query and suggest solutions accordingly, rather then interpreting the query and making assumptions.

In the meantime, go through below post and see if you find it helpful.

Monthly workbook with daily worksheets...Help please

Hi, *First timer here* I'm hoping someone can help - i may be overthinking things so apologies in advance. I use a monthly workbook with worksheets for days of the month to capture transport usage data... so 30-31 sheets in each workbook for every month. Is there a way to simplify this...

www.mrexcel.com

hth....He

Hello @fadee2

I am not able to attach an excel file here. I am new to the forum so I need to learn how to do it. Is there a special way to do it?

 

[fadee2]

Go through forum rules to get familiar with rules and regulations of the board.

You can use any free file sharing service like google drive or dropbox or onedrive, to upload your file and share the link in the thread.

 

[xavisaus]

Hello @fadee2

Here is the link to the sample.
WORKBOOK.xlsx

I created two template sheets:

• "Template xx" to use when creating sheets with only 2 digits
• "Template xxx" to use when creating sheets with more than 2 digits without "-" or "_"
• "Template -" to use when creating sheets with "-" on GENERAL P/N column or "_" on S column is work in progress...

The workbook have already a macro to order all the sheets alphabetically

Many thanks

 

[fadee2]

I m sorry, but I can not make anything from the workbook you provided, as each worksheet shows only errors, which I suppose is because of the formulae placed within. I'd suggest creating a workbook, with values only (paste special as values) and reuploading the workbook.

hth...

 

[xavisaus]

Hello @fadee2
The errors only occur on the templates, since the name of the sheet is not properly set. When you put a correct name, everything is filled out automatically, see sheet 10 or 10000 or whatever appart from the template