• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Inserting identical spreadsheet ?

tazz

tazz

Member
Hello ,
It is possible to insert a new spreadsheet with properties (tables, formulas,column width, etc) identical to a reference spreadsheet(Main) inside the same workbook without using copy-paste?
Thank you for your help
 
Hi tazz,

I don't know if I have correctly understood your problem however as per my understanding please try the below steps:

1. Right Click on spreadsheet(Main) and click on Move or Copy...

2. On dialog box appear where you need to select spreadsheet(Main) in Before Sheet: list.

3. There is one check box at the bottom which states Create a copy, check this box and click OK.

4. Now you will get the identical copy of the same sheet, now you need to rename it as per requirement.

Hope this can help you.

Thanks & Regards,
Anupam Tiwari
 
Anupam, thank you for your reply.
What I would like to do is to keep the Main hidden and when I click on Insert worksheet to have a sheet identical to Main.
Perhaps a VBA will solve this but I do not have enough knowledge to make it.
Thanks again.
 
Hi ,

Try this code in the Workbook section of your VBE :
Code: 
Private Sub Workbook_NewSheet(ByVal Sh As Object)
            Dim sheet_template As String
            sheet_template = "Sheet1"
           
            sh_name = Sh.Name
           
            Application.DisplayAlerts = False
            Application.ScreenUpdating = False
           
            Sh.Delete
           
            Worksheets(sheet_template).Copy After:=Worksheets(sheet_template)
            ActiveSheet.Name = sh_name
           
            Application.ScreenUpdating = True
            Application.DisplayAlerts = True
End Sub
Narayan
 
Hi,

Here is another way without VBA.

1) Place the workbook with only your Mainsheet/any other sheets required along with Main at your userprofile\Microsoft\Templates folder(%appdata%\Roaming\Microsoft\Templates) in Windows 7.

2) Right click on the sheet tab -> Insert and you will have your sheet/workbook listed under "General" section.

If this doesn't work with .xls or .xlsx , try converting the workbook to template (.xlt, .xltx) and place under the templates folder.
 
Hi Narayan,

You above provided code is really very nice however executing it step by step and keeping "Main" sheet hidden, I found a little issue mentioned below:

If the "Main" sheet is hidden that time the code is not working in the desired manner.

When the "Main" sheet is hidden and inserting a new sheet it inserting two sheet one is Identical to "Main" and one more sheet that remain hidden thus it creates as many hidden sheets as many times we insert a new sheet.

Please help to resolve this issue.

Thanks & Regards,
Anupam
 
Good day Taz

Would it be just as quick to un-hide the main and follow Anupam Tiwari's suggestion right click the main sheets tab and select copy, place it where you want in the workbook and then re-hide the main?
 
Hello ,
After Anupam brought those 2 issues I actually discovered that if I delete one of the duplicates it will delete or rename another page from the work book.
I will keep Main visible and I will work this way(not a problem).
It will be nice to have this problem solved just for future references in case some one else would like to have something similar.
Thank you all.
 
Hi Anupam ,

Can you try this , and give feedback ?
Code: 
Private Sub Workbook_NewSheet(ByVal Sh As Object)
            Dim temp As XlSheetVisibility
            Dim sheet_template As String
            sheet_template = "Sheet1"
         
            sh_name = Sh.Name
         
            Application.DisplayAlerts = False
            Application.ScreenUpdating = False
         
            Sh.Delete
         
            If Worksheets(sheet_template).Visible <> xlVisible Then
              temp = Worksheets(sheet_template).Visible
              Worksheets(sheet_template).Visible = True
              Worksheets(sheet_template).Copy After:=Worksheets(sheet_template)
              ActiveSheet.Name = sh_name
              Worksheets(sheet_template).Visible = temp
            End If
           
            Application.ScreenUpdating = True
            Application.DisplayAlerts = True
End Sub
Narayan
 
Hi Narayan,
I checked this one and is working. In hidden mode is not inserting any more duplicates and and is working normal when deleting spreadsheets.
It's a real nice features.
Thank you
 
You must log in or register to reply here.
Back
Top