I have a workbook with 2 worksheets. Each cell (10 rows by 25 columns worth)
in the last worksheet adds the value from the corresponding cell in the first
worksheet to a constant number (e.g. sheetlast.cell(A1) = sheet1.cell(A1)+5).
I have created a maco to make a copy of the first worksheet for a total of
3 worksheets. I want to add to the macro to update the formulas in the last
worksheet to look something like this
sheetlast.cell(A1) = sheet1.cell(A1)+sheet2.cell(A1) + 5.
Anyone ideas how to do?

why not make five sheets
adding a sheet named first and a sheet named last (leave these blank).

then put the sheets to be summed between the first and last sheets in the
tab order. So the tabs would look like
First
Sheet1
Sheet2
Last
Summary

then in A1 of the summary sheet put
=Sum(First:Last!A1)+5

then drag fill down 10 and across 15

Now whenever you add a sheet, put it somewhere between first and last and it
will be included in the sum.

--
Rgards,
Tom Ogilvy


"Scott" wrote:

> I have a workbook with 2 worksheets. Each cell (10 rows by 25 columns worth)
> in the last worksheet adds the value from the corresponding cell in the first
> worksheet to a constant number (e.g. sheetlast.cell(A1) = sheet1.cell(A1)+5).
> I have created a maco to make a copy of the first worksheet for a total of
> 3 worksheets. I want to add to the macro to update the formulas in the last
> worksheet to look something like this
> sheetlast.cell(A1) = sheet1.cell(A1)+sheet2.cell(A1) + 5.
> Anyone ideas how to do?

Thanks Tom. Could I use this Sheets(Sheets.Count).Name To determine the
last sheet name? Would it then look like

=Sum(First:Sheets(Sheets.Count).Name!A1)+5

----------------------------------------------------------------------------------------
"Tom Ogilvy" wrote:

> why not make five sheets
> adding a sheet named first and a sheet named last (leave these blank).
>
> then put the sheets to be summed between the first and last sheets in the
> tab order. So the tabs would look like
> First
> Sheet1
> Sheet2
> Last
> Summary
>
> then in A1 of the summary sheet put
> =Sum(First:Last!A1)+5
>
> then drag fill down 10 and across 15
>
> Now whenever you add a sheet, put it somewhere between first and last and it
> will be included in the sum.
>
> --
> Rgards,
> Tom Ogilvy
>
>
> "Scott" wrote:
>
> > I have a workbook with 2 worksheets. Each cell (10 rows by 25 columns worth)
> > in the last worksheet adds the value from the corresponding cell in the first
> > worksheet to a constant number (e.g. sheetlast.cell(A1) = sheet1.cell(A1)+5).
> > I have created a maco to make a copy of the first worksheet for a total of
> > 3 worksheets. I want to add to the macro to update the formulas in the last
> > worksheet to look something like this
> > sheetlast.cell(A1) = sheet1.cell(A1)+sheet2.cell(A1) + 5.
> > Anyone ideas how to do?

No. You are mixing VBA with Worksheet formulas.

--
Regards,
Tom Ogilvy


"Scott" <(E-Mail Removed)> wrote in message
news:95BE91EF-002E-4CA4-BF36-(E-Mail Removed)...
> Thanks Tom. Could I use this Sheets(Sheets.Count).Name To determine
> the
> last sheet name? Would it then look like
>
> =Sum(First:Sheets(Sheets.Count).Name!A1)+5
>
> ----------------------------------------------------------------------------------------
> "Tom Ogilvy" wrote:
>
>> why not make five sheets
>> adding a sheet named first and a sheet named last (leave these blank).
>>
>> then put the sheets to be summed between the first and last sheets in the
>> tab order. So the tabs would look like
>> First
>> Sheet1
>> Sheet2
>> Last
>> Summary
>>
>> then in A1 of the summary sheet put
>> =Sum(First:Last!A1)+5
>>
>> then drag fill down 10 and across 15
>>
>> Now whenever you add a sheet, put it somewhere between first and last and
>> it
>> will be included in the sum.
>>
>> --
>> Rgards,
>> Tom Ogilvy
>>
>>
>> "Scott" wrote:
>>
>> > I have a workbook with 2 worksheets. Each cell (10 rows by 25 columns
>> > worth)
>> > in the last worksheet adds the value from the corresponding cell in the
>> > first
>> > worksheet to a constant number (e.g. sheetlast.cell(A1) =
>> > sheet1.cell(A1)+5).
>> > I have created a maco to make a copy of the first worksheet for a
>> > total of
>> > 3 worksheets. I want to add to the macro to update the formulas in the
>> > last
>> > worksheet to look something like this
>> > sheetlast.cell(A1) = sheet1.cell(A1)+sheet2.cell(A1) + 5.
>> > Anyone ideas how to do?