OK, you have some steps to follow. I am going to assume you are not familiar with VBA.
Save your template to a folder where it is going to live.
In Excel press ALT+F11. This will open the VBA development window.
On the left side of the window, you will see a list of open files. At the top you should see PERSONAL.xlsb.
Right-click on PERSONAL.xlsb and select Insert > Module
If you do not have any macros yet, you will now have a folder under PERSONAL.xlsb called Modules, and a component under that called Module1.
Double click on Module 1. You should see a blank window to the right. This is the code for Module1.
Copy the following code and paste it into that window. Update the red part to match your path.
Option Explicit
' Apply formatting from the template to the active worksheet
Public Sub ApplyFormatTemplate()
Dim Template As Workbook
Dim TargetSheet As Worksheet
Const TemplateName = "C:\Users\YOUR PATH HERE\Procurement Template.xlsx"
' Save the currently active sheet, because
' opening the new workbook will change what is active
Set TargetSheet = ActiveSheet
' Open the template file
Set Template = Workbooks.Open(FileName:=TemplateName)
' Copy the formats from the template and paste them into the target
Template.Worksheets(1).Cells.Copy
TargetSheet.Cells.PasteSpecial Paste:=xlPasteFormats
' Clear the clipboard to suppress any prompts about saving what is on the clipboard
Application.CutCopyMode = False
' Close the template, suppressing any prompts
Application.DisplayAlerts = False
Template.Close savechanges:=False
Application.DisplayAlerts = True
' Restore the original active sheet
TargetSheet.Activate
End Sub
Click the little "save" icon in the menu bar of the VBA environment. This will save changes to the PERSONAL.xlsb file.
Close the VBA window
Back in Excel, right-click on any button in the Ribbon and select Customize the ribbon....
Now you can create a custom group in the Ribbon, add a button to it, and assign the macro to the button. If it is not clear what to do from looking at the window, let me know and I will give more detailed instructions for this part.
Bookmarks