Weekly Meeting Agenda Workbook

I am creating a meetign agenda workbook that contains 52 worksheets, 1 for each week. I used a VBA Macro from another thread that allows me to name the worksheets based on a value in a cell. My difficulty is that the sheet name only updates when I enter something into a cell or the worksheet changes somehow. I would like all the worksheet names to update when the value in the date cell, which is A1, changes.

Any help would be appreciated.

Sincerely,
AJD

.
What data are you intending to enter in A1 ?

A1 on every worksheet contains the date of the meeting.

.
So ... if you changed the date on the first sheet, you would want all the other sheets to have the same date ?

Currently, each sheet has a formula in cell A1 that references cell A1 on the previous weeks sheet and adds 7 so that the date on the active sheet is 1 week later.

The marco I am using changes the worksheet tab name to the value in A1. But it only changes when I edit a cell in the worksheet. I would like the worksheet tab name to change to the correct name based on A1 if the value in A1 changes.

.

Please Login or Register  to view this content.

The above macro will create 52 sheets in a workbook. Each sheet correctly named for each week of the year.

You'll create a TEMPLATE sheet with the date of the FIRST SUNDAY OF THE YEAR entered in cell A1. Then run
the macro to create all the weeks of the year.

See attached.

THANK YOU!

So can I complete my formatting and layout of the agenda on the template and it will copy the completed format and layout?

Also, I have KPIs that I have my team enter into each week and there is a YTD column that sums. Is there a way to have it copy the weekly nad ytd KPI columns as well so that the3 YTD sums correclty?

.
Setup the TEMPLATE sheet as desired. Everything should copy over to all the other sheets.

Attachment 685422

The attached photo is of a single agenda worksheet. On the right side you will see YTD columns under each name. The YTD column sums the YTD from the previous week and the current week.

Any way to incoporate that into the creating of the 52 worksheets?

Also, once the 52 sheets are created, should I save the workbook as an xlsx and not an xlsm?

.
If formulas exist in a sheet that will be copied, those need to be copied as values. You can Google for copying formulas.

Excel will most likely force you to save the workbook as .xlsm . What have you tried so far and what was the result ?

Understood. The issue is that the YTD column on sheet1 is different than all the following sheets. is there a way to accomodate that difference?

.
If "Sheet 1" is in the group of newly created sheets ... After the original macro has completely run (created all the new sheets), reference a separate
macro that addresses the Sheet 1 YTD Column.

Uniderstood
thank you