Back to Samples Menu

VBA Samples

Microsoft Excel

Copy a Master Worksheet

You've created a master sheet, and you'd like to copy it quickly as a template for a new worksheet. This works great for timesheets, monthly data input sheets, and other applications where you have worksheets with virtually the same layout.

This assumes that the worksheet you want to copy is called Master, and that it is hidden when you run the macro. Change the items in red text to suit your application.

Open your workbook. Hit Alt+F11 to view the VB Editor. Double-click ThisWorkbook. Paste the following code into the code window:

Dim NewPageName As String

Sub NewPage()

Sheets("Master").Visible = True
Sheets("Master").Copy After:=Worksheets(Worksheets.Count)
NewPageName = InputBox("What would you like to call your new Worksheet")
ActiveWindow.ActiveSheet.Name = NewPageName
Sheets("Master").Visible = False

End Sub