Luis Diaz
asked on
Excel VBA: create sheet template
Hello Experts,
I record and customise the following procedure in order to add a specific sheet template to my personal.xlsb.
I was wondering if you can help me to clean the code and remove unnecessary lines.
If you have questions. Please contact me
Sheet-Template-Creation-10092018.xlsm
I record and customise the following procedure in order to add a specific sheet template to my personal.xlsb.
I was wondering if you can help me to clean the code and remove unnecessary lines.
Sub SheetTemplate()
With ThisWorkbook
Worksheets.Add After:=Worksheets(Worksheets.Count), Count:=1
ActiveSheet.Name = ActiveSheet.Name & "-Template"
End With
Range("A1").Select
ActiveWindow.DisplayGridlines = False
Rows("1:1").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5287936
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Columns("A:I").Select
Selection.ColumnWidth = 13
Rows("2:2").Select
ActiveWindow.FreezePanes = True
Range("A1").Select
Rows("1:1").RowHeight = 51
Range("A1").Select
Range("A1").Value = "Header A1"
Range("B1").Value = "Header B1"
Range("C1").Value = "Header C1"
Range("D1").Value = "Header D1"
Rows("1:1").Select
Selection.Font.Bold = True
End Sub
I attached example file.If you have questions. Please contact me
Sheet-Template-Creation-10092018.xlsm
ASKER
Hello Roy,
Thank you for your comment.
My goal is to create a worksheet template with specific format (fill color, freeze etc,) in order to reuse it whenever I want instead of creating manually the format.
I have already the specific format as reported above.
I just want to clean the code as it has been generated with vba recorder.
Thank you for your comment.
My goal is to create a worksheet template with specific format (fill color, freeze etc,) in order to reuse it whenever I want instead of creating manually the format.
I have already the specific format as reported above.
I just want to clean the code as it has been generated with vba recorder.
You don't need to run all that code each time. Formatting code is notoriously slow. Format a sheet exactly how you want it and save.
Is this to be a workbook template or is it a sheet to use several times in one workbook like the example that I posted.
Is this to be a workbook template or is it a sheet to use several times in one workbook like the example that I posted.
ASKER
Yes, you are right, however I would like to add this to my personal macro so with one shortcut key I can generate the sheet instead of opening the template workbook and save it with another name. The goal is to generate the template in any workbook this is why I want to add it as a personal macro. I already have the procedure I just want to clean up all unecesarry lines.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This is the only code that you will need if you follow my suggestion.
Sub CreateCopy()
''/// copies template stored in PERSONAL.xlsb
ThisWorkbook.Sheets("Template").Copy after:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
ActiveSheet.Name = ActiveSheet.Name & "-Template"
End Sub
ASKER
Ok, thank you.
I was able to clean up the code. And this is my final result:
I was able to clean up the code. And this is my final result:
Sub Create_Sheet_Template()
Dim wb As Workbook
Dim ws As Worksheet
Set wb = ThisWorkbook
'Add new Sheet with "template" string
With wb
Worksheets.Add After:=Worksheets(Worksheets.Count), Count:=1
ActiveSheet.Name = ActiveSheet.Name & "-Template"
End With
Set ws = ActiveSheet
With ws
'Remove gridlines
ActiveWindow.DisplayGridlines = False
'Apply Borders
With .Range("A1:T400").Borders
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
'Set columns width
With .Columns("A:T")
.ColumnWidth = 14
End With
With .Rows("1:1")
'Fill first row with green
With .Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5287936
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With .Font
'Set font bold
.Bold = True
'Set font color white
.ColorIndex = 2
End With
'Set row height
.RowHeight = 51
End With
'Freeze pane as of first row
With .Rows("2:2").Select
ActiveWindow.FreezePanes = True
End With
End With
End Sub
Ignore good advice again
ASKER
Both solutions works. Thank you for your help.
I appreciate yours works but my suggestion will be a lot simpler. Anyway so long as you have it working.
By the way, your code won't work as you expect. The line below will refer to the PERSONAL.xlsb because it contains the code. You need to use ActiveWorkBook, ThisWorkBook will always refer to the workbook that actually holds the code.
By the way, your code won't work as you expect. The line below will refer to the PERSONAL.xlsb because it contains the code. You need to use ActiveWorkBook, ThisWorkBook will always refer to the workbook that actually holds the code.
Set wb = ThisWorkbook
Usually a template is created manually then saved. Something like this attached example
Basic-Invoice.xls