How to always have the same format in excel "new Sheet"

Question

Hello,

I am working in a company that wants me to create a template in excel. They want the template to be in the "Page Layout" format, where the template has a header (that has a title and a logo), a footer, and many rows and columns in between the header and footer that contain several titles and empty cells to fill out information. After finishing up with the first sheet, they always need to press "New Sheet" to fill out the form again but in a new sheet, where they require the same template. The company told me that it is a hassle to just copy-paste all of the template every time because apart from having to copy-paste all of the rows and columns in between the header and the footer into the new sheet, they also have to manually find the company logo and paste it in the header along with all of the information in the header as well. Basically, what I am looking for is a way for each time "New Sheet" is pressed, the template is always there, ready to fill.

If this requires a Macro, please let me know. I am willing to use code for this. Thank you very much.

-Sebastian Work

Answer 1

Hi Sebastian

Here is a macro solution to your problem.

I choose an Invoice template as a sample file for this solution

Please, try the following

1. Select a cell in your Template and create a Data Validation dropdown list with just one value, "New Template"

Here we used cell A1

2. Right-click on the sheet tab/ Click View Code, to lead you to the VBA panel

3. Select/DoubleClick the Template sheet to open the Worksheet Change Event Panel. Then paste the following VBA code, as indicated in the picture below.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Application.Intersect(Target, **Range("A1")**) Is Nothing Then 

            If Target.Value = "New Sheet" Then 

               Application.ScreenUpdating = False 

                        ActiveSheet.Copy After:=Sheets(Sheets.Count) 

                        With ActiveSheet 

                                .Range("**A15:D15**").Value = "" 

                                .Range("**A18:C36**").Value = "" 

                                .Range("**C9:C12**").Value = "" 

                                .Range("**A1**").Value = ""   ''' Resets the dropdown to nothing/blank 

                        End With 

               Application.ScreenUpdating = True 

            End If 

    End If 

End Sub

4. Save the workbook as a Macro Enable Excel File or Macro Enable Template

Important note:

This part of the code resets (clears) the values in the cells or range of cells you use to complete the form.

.Range("A15:D15").Value = ""

.Range("A18:C36").Value = ""

.Range("C9:C12").Value = ""

Similarly with the Dropdown cell

.Range("A1").Value = "" ''' Resets the dropdown, clears it/blank

Please, adapt/change the ranges according to your real scenario.

In this sample file, it clears these ranges

The macro will work upon selection of "New Sheet" in the dropdown

RESULTS:

Do let me know if you need more help

Regards

Jeovany

Answer 2

Hello Jeovany,

Thank you very much for your help. I followed all of your steps and made the modifications. but for some reason whenever I press "New Sheet", the new sheet is blank. Is there anything else I have to consider?

Once again, thank you very much.

Kind regards,

Sebastian

Answer 3

Hello Jeovany,

Thank you very much for your help. I followed all of your steps and made the modifications. but for some reason whenever I press "New Sheet", the new sheet is blank. Is there anything else I have to consider?

Once again, thank you very much.

Kind regards,

Sebastian

Hi Sebastian

Kindly suggest preparing and uploading your Invoice Template file to Onedrive, Dropbox, etc ... 

and share the link here.

This will help us to give you a prompt and right solution.

If you need help with how to upload the file please, click the link below

https://support.office.com/en-us/article/share-onedrive-files-and-folders-9fcc2f7d-de0c-4cec-93b0-a82024800c07

You may also follow the instructions in this video

https://www.youtube.com/watch?v=NnXsE0SNuCc&t=14s

Regards

Jeovany