Modifying formulas through code

[Rick B]

I have a workbook which contains a summary worksheet and numerous other
worksheets. The contents of each of the worksheets are identical. The
purpose of the summary worksheet is simply to sum the data in each of the
corresponding cells for each worksheet. The problem arises because the
actual number of worksheets is not finite. Users actually add a new
worksheet each week based off a template worksheet. The worksheet are named
with the week ending date (i.e 1-15-04)

Is there a way to modify the formulas on the Summary worksheet through code
to reflect the new worksheets that are added each week rather than having to
manually modify the formulas every week?

Secondly, there are also cells containing text data with general comments.
Is it possible to copy the contents of this text data from the weekly
worksheet to the Summary worksheet by copying into a specific cell, if the
cell has text in it, copy it to the cell below it, etc. With each text entry
in the Summary worksheet, the worksheet name the data came from should be
inserted in the cell to the right of the text.

Any guidance one could offer would be greatly appreciated.

Rick

 

[NJD]

I have a workbook which contains a summary worksheet and numerous other
worksheets. The contents of each of the worksheets are identical. The
purpose of the summary worksheet is simply to sum the data in each of the
corresponding cells for each worksheet. The problem arises because the
actual number of worksheets is not finite. Users actually add a new
worksheet each week based off a template worksheet. The worksheet are named
with the week ending date (i.e 1-15-04)

Is there a way to modify the formulas on the Summary worksheet through code
to reflect the new worksheets that are added each week rather than having to
manually modify the formulas every week?

Yes. VBA code can rewrite formulas.

Secondly, there are also cells containing text data with general comments.
Is it possible to copy the contents of this text data from the weekly
worksheet to the Summary worksheet by copying into a specific cell, if the
cell has text in it, copy it to the cell below it, etc. With each text entry
in the Summary worksheet, the worksheet name the data came from should be
inserted in the cell to the right of the text.

Yes. Record a macro, do it manually, and you'll see the code. Same for
above.