Applying Predetermined Formatting/Logic to a Downloaded Table

Every week I generate a report that has dates, names, and lead times (number of calendar days). The generated report is just a simple Excel table with headers and no other formatting. I want to create rules for each column through conditional formatting, however I don't want to have to re-write (or copy/paste) the rules to the new report...every week...that the report is downloaded.

Is there a way to save my rules/logic/formatting ---> download the simple Excel table without formatting ---> re-format that Excel table to my pre-determined formatting/logic/rules?

I will be doing this weekly, so I'm trying to minimize the amount of clicks required. Ideally, this done in an single Excel file that I upload the simple Excel table to, auto re-format, and Save As "Week of ___" every week.

Thanks!

You can:

Have a preformatted template file with the desired formatting. Go to your table, and copy it. Go back to your template and Paste Formulas, which will paste all values and formulas but not affect the formatting.

or

Have a preformatted template file with the desired formatting. Go to the template and copy it. Go to your table, Paste Formats.

This could also be automated as one click if you want to use VBA. To actually write the VBA we would need a file sample.

There is no one-click solution built-in for what you want to do.

Excel has cell styles, but they do not support conditional formatting, and these are for individual cells, not an entire sheet.

You can define a table style for an entire structured table, but it's only for font, border, and fill, not numeric formats, and not for conditional formatting.

The one-click way to say "take all of the formatting on this sheet and apply it to this other sheet" are the two methods I described (well, OK, two clicks). You obviously have to at least select the source sheet and the destination sheet, so this logically cannot be done in one click.

If you use a macro, you could add a custom button to the Ribbon to do it in one click. The code would retrieve the formatting from the saved template and apply it to the currently active sheet.

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.

Please Login or Register  to view this content.

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.

Yes, it uses the current version of the template every time so changes will be applied.

You tried to put a sub in the middle of another sub. Illegal syntax. Simplest fix is remove the Sub and End Sub lines.